Snowflake parsing JSON and add aggrate column

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

1 answer

  • answered 2022-05-06 16:10 Gokhan Atil

    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" |
    +-----------+-----+---------+-----------------+
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum