我试图从一个名为json\u table的表中解析一个json列，这个表有两种不同类型的元素，
keyfield | json_column | SomeotherField1 | SomeotherField2
----------------------------------------------------------------
keyfield1, | {"jField1":"Value1", ..."jField10":"Value10", "MapField":[{"Key": "key1", "Value":"Keyvalue1"}, {"Key": "key2", "Value":"Keyvalue2"}] | someothervalue | someothervalue使用get_json_object函数，我可以很容易地访问jfield1到jfield10和mapfield。但我不知道如何将mapfield解析成更多的独立列。
SELECT keyfield, get_json_object(json_column, '$.jField1') as jField1, get_json_object(json_column, '$.jField2') as jField2
FROM Json_table我需要写一个查询来提供这样的结果
Select Keyfield, jField1, jField2, .. , jField10, Key1, Key2 From Json_table
result as:
keyfield1 | Value1 | ... | Value10 | Keyvalue1 | Keyvalue2我想从“mapfield”部分中获取所有单个元素。
1条答案
按热度按时间dgenwo3n1#
您可以访问
MapField按索引数组，$.MapField[0].Value, $.MapField[1].Value```with cte as (
select string('{"jField1":"Value1","jField10":"Value10", "MapField":[{"Key": "key1", "Value":"Keyvalue1"}, {"Key": "key2", "Value":"Keyvalue2"}]}')as jsn_col)
select get_json_object(jsn_col,'$.jField1')jField1,
get_json_object(jsn_col,'$.MapField[0].Value')key1,
get_json_object(jsn_col,'$.MapField[1].Value')key2 from cte;
+-------+---------+---------+
|jField1|key1 |key2 |
+-------+---------+---------+
|Value1 |Keyvalue1|Keyvalue2|
+-------+---------+---------+