Snowflake parsing JSON and add aggrate column

Question

have a json field

[
    {'id':'1', 'name':'test1', 'address':'123 Main St'},
    {'id':'1', 'name':'test2', 'address':'1404 Burke St'}
]

This is how the records are arriving and I would want to parse the json and add additional column that is order which will tell if it's first record or second

so far i have:

select to_variant(parse_json(column)):id, to_variant(to_json(column)):name, to_variant(to_json(column)):address from the table

but cannot add the order column

Answer 1

Can you try something like this?

select json.index + 1 record_no, json.value:id id, json.value:name name , json.value:address address from
(
    select parse_json($$ [ {'id':'1', 'name':'test1', 'address':'123 Main St'},
    {'id':'1', 'name':'test2', 'address':'1404 Burke St'} ] $$) c1
),
lateral flatten( parse_json(c1 )) json;

+-----------+-----+---------+-----------------+
| RECORD_NO | ID  |  NAME   |     ADDRESS     |
+-----------+-----+---------+-----------------+
|         1 | "1" | "test1" | "123 Main St"   |
|         2 | "1" | "test2" | "1404 Burke St" |
+-----------+-----+---------+-----------------+