presto json

Presto是一种用于分布式数据处理的开源查询引擎，它支持使用SQL语言对各种数据源进行查询。在Presto中，可以使用多种方式来解析JSON并获取字段值。

根据引用，可以使用GET_JSON_OBJECT函数来解析JSON并提取字段值。示例SQL语句如下：
SELECT GET_JSON_OBJECT(json_str, '$.id') AS sid
FROM (
SELECT json_str
FROM (
SELECT split(regexp_replace(regexp_extract(event_param_json, '^\\[(.*)\\]$', 1), '\\}\\,\\{', '\\}\\|\\|\\{'), '\\|\\|') AS json_list
FROM abm.abm_wireless_exposure_log
WHERE dt = '2020-09-06'
AND page_id = 'ManChannel'
AND event_param_json LIKE '%ass%'
) a
LATERAL VIEW explode(json_list) list_tab AS json_str
) t

引用中提到了另一种使用GET_JSON_OBJECT函数的方法来解析JSON并获取字段值。

还可以使用JSON_EXTRACT_SCALAR函数来解析JSON并提取字段值，如引用所示。示例SQL语句如下：
SELECT str_json, JSON_EXTRACT_SCALAR(str_json, '$.id') AS sid
FROM (
SELECT event_param_json
FROM abm.abm_wireless_exposure_log
WHERE dt = '2020-09-06'
AND event_id = 'NecProExpo'
AND event_param_json LIKE '%ass%'
) CROSS JOIN unnest(SPLIT(REPLACE(REPLACE(REPLACE(event_param_json, '[', ''), ']', ''), '},{', '}#{'), '#')) AS t(str_json)

使用以上的SQL语句，你可以在Presto中解析JSON并获取字段值。<span class="em">1</span><span class="em">2</span><span class="em">3</span>
#### 引用[.reference_title]
- *1* *2* *3* [hive与presto解析json数组并拆分为多行](https://blog.csdn.net/lzxlfly/article/details/108458110)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 100%"]
[ .reference_list ]