如何在Presto中提取JSON值？提取列名与类型遇空值问题

问题原因 #

你用JSON_EXTRACT_SCALAR返回null的核心原因是：这个函数仅支持提取单个标量值（字符串、数字、布尔值等），但$.dataResource.columns是一个JSON数组（复合类型），不符合该函数的适用场景，因此返回null。

解决方法 #

1. 获取完整的columns数组（保留JSON格式）

如果只需要拿到整个columns数组的JSON内容，改用JSON_EXTRACT函数（支持提取复合JSON类型）：

SELECT 
    JSON_EXTRACT(json_dump, '$.dataResource.columns') AS columns_array
FROM your_table;

2. 将数组展开为多行，单独提取columnName和columnType

如果需要把每个列的信息拆成独立行，根据你使用的SQL引擎选择对应写法：

BigQuery 写法

SELECT 
    JSON_EXTRACT_SCALAR(column_item, '$.columnName') AS column_name,
    JSON_EXTRACT_SCALAR(column_item, '$.columnType') AS column_type
FROM your_table,
UNNEST(JSON_EXTRACT_ARRAY(json_dump, '$.dataResource.columns')) AS column_item;

MySQL 写法

SELECT 
    j.columnName,
    j.columnType
FROM your_table,
JSON_TABLE(
    json_dump,
    '$.dataResource.columns[*]' COLUMNS (
        columnName VARCHAR(255) PATH '$.columnName',
        columnType VARCHAR(255) PATH '$.columnType'
    )
) AS j;

PostgreSQL 写法

SELECT 
    (column_item ->> 'columnName') AS column_name,
    (column_item ->> 'columnType') AS column_type
FROM your_table,
json_array_elements(json_dump::json -> 'dataResource' -> 'columns') AS column_item;

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