AWS Redshift SQL：检测JSON对象是否有键值对及提取所有键方法问询

一、检测JSON对象是否包含有效键值对 #

针对预先未知键的场景，可通过提取JSON对象的键数组并判断其长度来实现。以你提供的示例为例，假设表中存在存储目标JSON的字段json_data，SQL语句如下：

SELECT
  json_data,
  -- 判断context子对象是否有有效键值对
  CASE
    WHEN array_length(json_object_keys(json_extract_path(json_data, 'context')), 1) > 0 THEN '是'
    ELSE '否'
  END AS has_valid_key_value
FROM your_target_table;

逻辑说明： #

1. json_extract_path(json_data, 'context')：从JSON中提取context对应的子对象；
2. json_object_keys(...)：将子对象的所有键转换为数组；
3. array_length(..., 1)：获取数组的长度，若长度大于0则说明存在有效键值对，否则为空对象。

如果需要检测整个顶级JSON对象而非某个子对象，只需去掉json_extract_path部分，直接使用json_object_keys(json_data)即可。

二、提取JSON对象中的所有键 #

根据需求分为提取顶层键和提取所有嵌套键两种场景：

1. 提取顶层键 #

直接使用json_object_keys函数即可获取JSON的所有顶层键：

SELECT
  json_data,
  json_object_keys(json_data) AS top_level_keys
FROM your_target_table;

2. 提取所有嵌套键（含层级路径） #

若需要提取包括嵌套对象在内的所有键，并保留层级路径（如context.abc），可通过递归CTE实现：

WITH RECURSIVE nested_json_keys AS (
  -- 初始层：获取顶层键及对应值
  SELECT
    json_data AS original_json,
    CAST('' AS VARCHAR(MAX)) AS parent_key,
    json_object_keys(json_data) AS key_name,
    json_extract_path(json_data, json_object_keys(json_data)) AS key_value
  FROM your_target_table
  UNION ALL
  -- 递归层：处理嵌套JSON对象，继续提取键
  SELECT
    njk.original_json,
    -- 拼接父键路径
    CASE WHEN njk.parent_key = '' THEN njk.key_name ELSE njk.parent_key || '.' || njk.key_name END AS parent_key,
    json_object_keys(njk.key_value) AS key_name,
    json_extract_path(njk.key_value, json_object_keys(njk.key_value)) AS key_value
  FROM nested_json_keys njk
  -- 仅对JSON对象类型的值进行递归提取
  WHERE json_typeof(njk.key_value) = 'object'
)
-- 去重并输出完整键路径
SELECT DISTINCT
  CASE WHEN parent_key = '' THEN key_name ELSE parent_key || '.' || key_name END AS full_key_path
FROM nested_json_keys;

注意事项： #

• 确保你的Redshift集群支持json_object_keys、json_typeof等函数（Redshift 1.0.1250及以上版本已支持）；
• 若JSON中包含数组类型，上述递归逻辑不会提取数组内的键，如需处理数组可额外添加判断分支。

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