Redshift中仅用SELECT语句展开JSON数组的问题求助

错误原因说明 #

你遇到的function json_array_length(character varying, "unknown", "unknown") does not exist错误，是因为Redshift的json_array_length函数仅接受单个JSON类型参数，你传入了多余参数才导致报错。

核心解决方案（单SELECT语句） #

利用Redshift的GENERATE_SERIES生成数组索引序列，结合JSON_EXTRACT_ARRAY_ELEMENT_TEXT拆分数组元素，再用JSON_EXTRACT_PATH_TEXT解析字典字段，全程用单SELECT语句实现。

场景1：JSON字段本身就是数组 #

假设你的数据表名为api_responses，存储JSON的字段为response_json，数组每个元素是字典结构：

SELECT
  ar.id, -- 主表其他业务字段
  -- 提取数组元素中具体键值
  JSON_EXTRACT_PATH_TEXT(
    JSON_EXTRACT_ARRAY_ELEMENT_TEXT(ar.response_json::JSON, idx),
    'user_id'
  ) AS user_id,
  JSON_EXTRACT_PATH_TEXT(
    JSON_EXTRACT_ARRAY_ELEMENT_TEXT(ar.response_json::JSON, idx),
    'order_amount'
  ) AS order_amount
FROM
  api_responses ar,
  -- 生成从0到数组长度-1的索引序列
  GENERATE_SERIES(
    0,
    JSON_ARRAY_LENGTH(ar.response_json::JSON) - 1
  ) AS idx
WHERE
  -- 过滤非数组类型的记录，避免报错
  JSON_TYPEOF(ar.response_json::JSON) = 'array'

场景2：数组嵌套在JSON的某个键下 #

如果数组是JSON对象里的一个子字段（比如response_json中的"orders": [{}, {}]）：

SELECT
  ar.id,
  JSON_EXTRACT_PATH_TEXT(elem, 'product_name') AS product_name,
  JSON_EXTRACT_PATH_TEXT(elem, 'quantity') AS quantity
FROM
  api_responses ar,
  -- 先提取嵌套的数组，再生成索引
  GENERATE_SERIES(
    0,
    JSON_ARRAY_LENGTH(JSON_EXTRACT_PATH_TEXT(ar.response_json, 'orders')::JSON) - 1
  ) AS idx,
  -- 提取数组中指定索引的元素
  (SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT(JSON_EXTRACT_PATH_TEXT(ar.response_json, 'orders'), idx)) AS elem
WHERE
  -- 确保嵌套字段是数组类型
  JSON_TYPEOF(JSON_EXTRACT_PATH_TEXT(ar.response_json, 'orders')::JSON) = 'array'

兼容BI工具的替代方案（无GENERATE_SERIES） #

如果你的BI工具不支持GENERATE_SERIES，可以用手动构造的数字序列替代（假设数组最大长度不超过4）：

SELECT
  ar.id,
  JSON_EXTRACT_PATH_TEXT(
    JSON_EXTRACT_ARRAY_ELEMENT_TEXT(ar.response_json::JSON, nums.n),
    'key_name'
  ) AS key_value
FROM
  api_responses ar,
  -- 手动构造数字索引，按需扩展长度
  (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) nums
WHERE
  JSON_TYPEOF(ar.response_json::JSON) = 'array'
  -- 只取数组实际存在的索引
  AND nums.n < JSON_ARRAY_LENGTH(ar.response_json::JSON)

关键注意事项 #

• Redshift的JSON数组索引从0开始，所以生成序列要从0到数组长度-1
• 所有JSON操作前建议用::JSON显式转换字段类型，避免字符类型导致的函数报错
• 用JSON_TYPEOF过滤非数组记录，防止处理单值JSON时出现索引越界错误

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