snowflake json lateral subquery

Viewed 1547

I have the following in snowflake:

create or replace table json_tmp as select column1 as id, parse_json(column2) as c
    from VALUES (1,
                 '{"id": "0x1",
                   "custom_vars": [
                      { "key": "a", "value": "foo" },
                      { "key": "b", "value": "bar" }
                   ] }') v;

Based on the FLATTEN docs, I hoped to turn these into a table looking like this:

+-------+---------+-----+-----+
| db_id | json_id |  a  |  b  |
+-------+---------+-----+-----+
+-------+---------+-----+-----+
|   1   |   0x1   | foo | bar |
+-------+---------+-----+-----+

Here is the query I tried; it resulted in a SQL compilation error: "Object 'CUSTOM_VARS' does not exist."

select json_tmp.id as dbid,
    f.value:id as json_id,
    a.v,
    b.v
from json_tmp,
    lateral flatten(input => json_tmp.c) as f,
    lateral flatten(input => f.value:custom_vars) as custom_vars,
    lateral (select value:value as v from custom_vars where value:key = 'a') as a,
    lateral (select value:value as v from custom_vars where value:key = 'b') as b;

What exactly is the error here? Is there a better way to do this transformation?

2 Answers
Related