未知字段名时用Oracle SQL解析JSON并过滤空值的方法

问题描述 #

给定如下JSON数据：

{"result":[
  {
   "field_name1":{"display_value":"TEST1","link":"https://test1.com","value":"abcd"},
   "field_name2":{"display_value":"","link":"","value":""},
   "field_name3":{"display_value":"TEST3","link":"https://test3.com","value":"efgh"},
   "field_name4":{"display_value":"TEST4","link":"https://test3.com","value":"ijkl"}
  }
]}

需要用Oracle SQL的SELECT语句解析该JSON，排除value为空的字段，得到如下格式结果：

field_name1:abcd
field_name3:efgh    
field_name4:ijkl

尝试过JSON_TABLE但因无法提前知晓字段名称未能实现，求解决方案。

解决方案 #

可以通过动态遍历JSON键值对结合条件过滤实现，具体方法如下：

核心SQL语句（针对JSON字符串） #

SELECT 
    key_name || ':' || JSON_VALUE(json_data, '$.result[0].' || key_name || '.value') AS result_line
FROM (
    -- 提取result数组第一个对象的所有字段名
    SELECT 
        json_value(jt.key_path, '$') AS key_name,
        t.json_data
    FROM 
        (SELECT '{"result":[{"field_name1":{"display_value":"TEST1","link":"https://test1.com","value":"abcd"},"field_name2":{"display_value":"","link":"","value":""},"field_name3":{"display_value":"TEST3","link":"https://test3.com","value":"efgh"},"field_name4":{"display_value":"TEST4","link":"https://test3.com","value":"ijkl"}}]}' AS json_data FROM dual) t,
        JSON_TABLE(JSON_KEYS(t.json_data, '$.result[0]'), '$[*]' COLUMNS key_path VARCHAR2(100) PATH '$') jt
) key_list
WHERE 
    -- 过滤value为空的字段
    JSON_VALUE(json_data, '$.result[0].' || key_name || '.value') IS NOT NULL
    AND JSON_VALUE(json_data, '$.result[0].' || key_name || '.value') <> '';

针对表存储的JSON数据（假设表名为json_store，字段名为json_content） #

SELECT 
    key_name || ':' || JSON_VALUE(t.json_content, '$.result[0].' || key_name || '.value') AS result_line
FROM 
    json_store t,
    JSON_TABLE(JSON_KEYS(t.json_content, '$.result[0]'), '$[*]' COLUMNS key_name VARCHAR2(100) PATH '$') jt
WHERE 
    JSON_VALUE(t.json_content, '$.result[0].' || key_name || '.value') IS NOT NULL
    AND JSON_VALUE(t.json_content, '$.result[0].' || key_name || '.value') <> '';

逻辑说明 #

1. JSON_KEYS：提取指定JSON对象（$.result[0]）的所有字段名，返回键名数组；
2. JSON_TABLE：将键名数组拆分成行数据，实现动态遍历所有字段；
3. 条件过滤：通过JSON_VALUE获取每个字段的value值，排除空值或空字符串的记录；
4. 结果拼接：将字段名与对应value拼接成要求的输出格式。

内容的提问来源于stack exchange，提问作者Coding Duchess