在Synapse中查询JSON数组并转为文本列的实现方法

两种场景的处理方案 #

场景1：原始JSON中skills为数组（未拆分） #

如果people.json里每个对象的skills本身就是数组，直接在OPENJSON的WITH子句中将skills标记为JSON类型，就能直接获取JSON数组格式的文本：

SELECT
    id,
    name,
    surname,
    age,
    skills AS skills_json_text
FROM
    OPENROWSET(
        BULK 'abfss://your-container@your-storage-account.dfs.core.windows.net/people.json',
        FORMAT = 'CSV',
        FIELDTERMINATOR = '0x0b',
        FIELDQUOTE = '0x0b'
    ) WITH (
        json_content NVARCHAR(MAX)
    ) AS j
CROSS APPLY OPENJSON(json_content)
WITH (
    id INT,
    name NVARCHAR(50),
    surname NVARCHAR(50),
    age INT,
    skills NVARCHAR(MAX) AS JSON  -- 保留原始数组的JSON文本格式
) AS people

场景2：已拆分skills数组为单行，需合并为JSON数组 #

如果之前的查询把skills数组拆成了单个skill的行，用STRING_AGG拼接成合法的JSON数组：

SELECT
    id,
    name,
    surname,
    age,
    CONCAT('[', STRING_AGG('"' + STRING_ESCAPE(skill, 'json') + '"', ','), ']') AS skills_json_text
FROM (
    -- 子查询：拆分skills数组为单行记录
    SELECT
        id,
        name,
        surname,
        age,
        skill.value AS skill
    FROM
        OPENROWSET(
            BULK 'abfss://your-container@your-storage-account.dfs.core.windows.net/people.json',
            FORMAT = 'CSV',
            FIELDTERMINATOR = '0x0b',
            FIELDQUOTE = '0x0b'
        ) WITH (
            json_content NVARCHAR(MAX)
        ) AS j
    CROSS APPLY OPENJSON(json_content)
    WITH (
        id INT,
        name NVARCHAR(50),
        surname NVARCHAR(50),
        age INT,
        skills NVARCHAR(MAX) AS JSON
    ) AS people
    CROSS APPLY OPENJSON(skills) AS skill
) AS split_skills
GROUP BY id, name, surname, age

注意事项 #

• STRING_ESCAPE用于转义skill中的特殊字符（如引号、反斜杠），保证生成的JSON格式合法，避免后续Logic Apps处理出错。
• 路径部分替换为你实际的Synapse数据湖存储路径，Serverless SQL Pool支持abfss://（ADLS Gen2）或https://（Blob存储）协议。

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