在Amazon Redshift中用json_extract_path_text提取嵌套JSON公司名称返回null

云上老周

2026-6-23

解决JSON_EXTRACT_PATH_TEXT提取数组内字段返回NULL的问题

你的SQL执行后返回NULL，核心原因是companies是JSON数组类型，而JSON_EXTRACT_PATH_TEXT无法直接跳过数组层级提取嵌套字段，必须先将数组展开为单独的行，再逐个提取每个对象中的name值。

正确的实现方式（以Redshift为例）

方法1：通过生成数组索引展开数组

WITH json_source AS (
    SELECT '{"topics": ["Fintech", "Artificial Intelligence", "Market Research & Consumer Insights"],
     "companies": [{"name": "TKO Group", "idCbiEntity": 1091583}, {"name": "All Elite Wrestling", "idCbiEntity": 1058641},
     {"name": "New Japan Pro Wrestling", "idCbiEntity": 276499}]}'::JSON AS input_json
),
array_positions AS (
    -- 生成数组元素的索引（从0开始）
    SELECT generate_series(0, JSON_ARRAY_LENGTH(input_json->'companies') - 1) AS idx
    FROM json_source
)
SELECT 
    JSON_EXTRACT_PATH_TEXT(input_json, 'companies', idx::VARCHAR, 'name') AS company_name
FROM json_source, array_positions;

方法2：使用UNNEST展开JSON数组（Redshift 1.0.25772及以上版本支持）

SELECT 
    JSON_EXTRACT_PATH_TEXT(company_item, 'name') AS company_name
FROM (
    SELECT UNNEST(JSON_PARSE(input_json->'companies')) AS company_item
    FROM (
        SELECT '{"topics": ["Fintech", "Artificial Intelligence", "Market Research & Consumer Insights"],
         "companies": [{"name": "TKO Group", "idCbiEntity": 1091583}, {"name": "All Elite Wrestling", "idCbiEntity": 1058641},
         {"name": "New Japan Pro Wrestling", "idCbiEntity": 276499}]}'::JSON AS input_json
    ) AS json_data
) AS expanded_companies;