如何在SingleStore(MemSQL)中使用JSON_TO_ARRAY扁平化JSON数组？

云原生阿岚

2026-8-15

嵌套JSON数组扁平化实现方案

要把source表中嵌套的JSON数组展开成结构化的dest表，核心是先定位到目标数组，再将数组拆分为单独行，最后提取字段。以下是不同SQL引擎的具体实现：

Hive SQL 实现

CREATE TABLE dest AS
SELECT
    -- 从数组元素中提取title字段
    get_json_object(row_element, '$.title') AS title,
    -- 提取count字段并转为数值类型（可选，根据需求调整）
    cast(get_json_object(row_element, '$.count') AS int) AS count
FROM
    source
-- 先取出results.rows的JSON字符串，转成数组后炸开
LATERAL VIEW EXPLODE(JSON_TO_ARRAY(get_json_object(data, '$.results.rows'))) exploded_rows AS row_element;

步骤说明：

get_json_object(data, '$.results.rows')：从data字段中提取results下的rows数组JSON字符串
JSON_TO_ARRAY(...)：将JSON字符串转换为Hive可识别的数组类型
LATERAL VIEW EXPLODE(...)：把数组的每个元素拆分成独立的行
最后从每个数组元素中提取title和count，并按需转换类型

Spark SQL 实现

如果用Spark SQL，也可以用更简洁的写法：

CREATE TABLE dest AS
SELECT
    title,
    count
FROM
    source,
    -- 直接解析数组为结构体并展开
    inline(from_json(get_json_object(data, '$.results.rows'), 'array<struct<title:string,count:int>>'));

MySQL 实现

MySQL用JSON_TABLE函数直接解析数组：

CREATE TABLE dest AS
SELECT
    j.title,
    j.count
FROM
    source,
    JSON_TABLE(
        source.data,
        '$.results.rows[*]' COLUMNS(
            title VARCHAR(255) PATH '$.title',
            count INT PATH '$.count'
        )
    ) AS j;