查询以数组开头的json

Question

我有一个以数组开头的JSON，但我无法查询它。JSON的格式如下：

[
{"@id":1,
"field1":"qwerty",
"@field2":{"name":"my_name", "name2":"my_name_2"},
"field3":{"event":[{"event_type":"OP",...}]}
},
{"@id":2..
}
]

对如何查询有什么建议吗？

如果我尝试使用横向展平，我不知道该使用什么键：

select 
'???'.Value:@id::string as id
from tabl1
,lateral flatten (tabl1_GB_RECORD:???) as gb_record

Answer 1

您的SQL已关闭但不完整，下面将为您提供@id值。

with tbl1 (v) as (
 select parse_json('  
 [
 {"@id":1,
 "field1":"qwerty",
 "@field2":{"name":"my_name", "name2":"my_name_2"},
 "field3":{"event":[{"event_type":"OP"}]}
 },
 {"@id":2
 }
 ]')
)  
select t.value:"@id" id from tbl1
, lateral flatten (input => v)  as t

结果：

id
___
1
2

如果您有任何其他问题，请告诉我

Answer 2

当json以数组开始时，您可以利用要展平的字段。大致是这样的：

WITH x AS (
SELECT parse_json('[
{"@id":1,
"field1":"qwerty",
"@field2":{"name":"my_name", "name2":"my_name_2"},
"field3":{"event":[{"event_type":"OP"}]}
},
{"@id":2,
"field1":"qwerty",
"@field2":{"name":"my_name", "name2":"my_name_2"},
"field3":{"event":[{"event_type":"OP"}]}
}
]') as json_data
)
SELECT y.value,
       y.value:"@id"::number as id,
       y.value:field1::string as field1,
       y.value:"@field2"::variant as field2,
       y.value:field3::variant as field3,
       y.value:"@field2":name::varchar as name
FROM x,
LATERAL FLATTEN (input=>json_data) y;