我正在try 从我的JSON列中解析出TEST-N-VALUE.我曾try 使用通配符，但此处创建的列仅返回NULL.
SELECT get_json_object('{"random_field": "ABC", "test-0-value":"1", "test-1-value":"2"}', '$.test-*-value');
有谁有任何关于如何从中提取这些动态对象的提示吗？预期结果将是..但请记住，输出必须允许动态数量的键值对.
extracted_out
-------------
{"test-0-value":"1", "test-1-value":"2"}
You can convert json to map using from_json, explode map to get key, value, filter only keys which you need,
collect array of key:value, concatenate array with comma delimiter, use str_to_map to get map containing keys which you need, then you can optionally convert it to JSON string using to_json
演示:
with src as (select '{"random_field": "ABC", "test-0-value":"1", "test-1-value":"2"}' as original_json)
select s.original_json, to_json( str_to_map(concat_ws(',',collect_list(concat(key,':',value))))) result
from src s
lateral view explode(from_json(s.original_json, 'map<STRING, STRING>')) m as key, value
where key rlike 'test-\\d+-value'
group by s.original_json
""").show(100, false)
结果:
+---------------------------------------------------------------+---------------------------------------+
|original_json |result |
+---------------------------------------------------------------+---------------------------------------+
|{"random_field": "ABC", "test-0-value":"1", "test-1-value":"2"}|{"test-0-value":"1","test-1-value":"2"}|
+---------------------------------------------------------------+---------------------------------------+
最新情况:
使用MAP_FILTER找到更好的解决方案
spark.sql("""
with src as (select '{"random_field": "ABC", "test-0-value":"1", "test-1-value":"2"}' as original_json)
select s.original_json, to_json( map_filter(from_json(s.original_json, 'map<STRING, STRING>'), (k, v) -> k rlike 'test-\\d+-value')) result
from src s
""").show(100, false)