Retrieve value from Snowflake JSON column

Question

i have a json column with each record as

[ { "DATE": 20190910 "NAME": "Tom" }, { "DATE": 20190504 "NAME": "Bob" } ]

The above is one record in the column.

I need to retrieve the record as Tom,Bob in a single record in the column.

Please refer the attached image for clarity.enter image description here

score 1 · Answer 1 · answered Apr 17 '20 at 02:10

so expanding the input data to multiple rows of input json, to show how to group it on values carried in the flatten pattern

with rows_of_json as (
    select parse_json(raw_json_string) as json 
    from values 
        ('[ { "DATE": 20190910, "NAME": "Tom1" }, { "DATE": 20190504, "NAME": "Bob1" } ]'),
        ('[ { "DATE": 20190910, "NAME": "Tom2" }, { "DATE": 20190504, "NAME": "Bob2" } ]')
        d(raw_json_string)
)
select array_agg(f.value:NAME::text) within group (order by f.value:DATE::number) as ordered_list  
from rows_of_json j, lateral flatten (input=>j.json) f
group by f.seq;

gives:

ORDERED_LIST
[    "Bob2",    "Tom2"  ]
[    "Bob1",    "Tom1"  ]

This is flattening the data, and using the seq inside the flatten to re-group the data on based on the rows that came from..

Also sorting the data based on a property in the object, if your wanting the original order, then in the above context:

array_agg(f.value:NAME::text) within group (order by f.index) as ordered_list

gives:

ORDERED_LIST
[    "Tom2",    "Bob2"  ]
[    "Tom1",    "Bob1"  ]

Retrieve value from Snowflake JSON column

1 Answers1