snowflake-cloud-data-platform - 雪花：数组中的 JSON 数据

Question

JSON数据如下

{name : Mike, job : [{name: abc, value: 123},{name: def,value: 456}]}

如何检索 name = abc 和 def 的值？

编辑：（解决方案）我自己得到了解决方案，谢谢

 WITH x AS (
SELECT parse_json('{"name" : "Mike", "job" : [{"name": "abc", "value": "123"},{"name": "def","value": "456"}]}' ) as payload_json)
    select x.payload_json:name,
           job.value:name::varchar as name,
           job.value:value::varchar as value
     from x,
    lateral flatten( input => x.payload_json:job, outer => true) as job;

标签： snowflake-cloud-data-platform

Answer 1

我自己得到了答案，如下所示

 WITH x AS (
SELECT parse_json('{"name" : "Mike", "job" : [{"name": "abc", "value": "123"},{"name": "def","value": "456"}]}' ) as payload_json)
    select x.payload_json:name,
           job.value:name::varchar as name,
           job.value:value::varchar as value
     from x,
    lateral flatten( input => x.payload_json:job, outer => true) as job;