SQL Server导入JSON子数组 如何提取为逗号分隔字符串

问题场景 #

现有SQL Server脚本功能为读取JSON文件并将数据插入数据表，处理数组类型字段时存在阻碍，对应JSON结构示例如下：

"top": 19.2743,
"bottom": 20.3115,
"left": 0.2878,
"right": 1.7038,
"isInternalToDevice": false,
"numberOfLegs": 3,
"legs": [
    3,
    2
],

目标效果：将legs字段的数组值提取为3,2格式的逗号分隔字符串。
现有可正常读取其余所有字段的代码如下，仅需补充legs字段（此处为双关玩笑，取legs“支撑腿”的字面义:-)）的正确提取逻辑即可：

DECLARE @JSONRoot VARCHAR(50)
SET @JSONRoot = '$._embedded.symbols'
 SELECT     
    [id],
    [description],
    [displayCategoryProgrammaticName],
    [displayCategoryProgrammaticNameDisplay],
    [manufacturer],
    [model],
    [modelqualifier],
    [ProgrammaticName],
    [Type],
    [Position],
    [Label],
    [ReceptacleType],
    [ConnectorType],
    [NumberOfLegs]
  FROM OPENROWSET (BULK 'D:\Test\TestFileSymbols.json', SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn, ''+@JSONRoot+'')
WITH
(
    [id] UNIQUEIDENTIFIER,
    [description] VARCHAR(200),
    [displayCategoryProgrammaticName] VARCHAR(50),
    [displayCategoryProgrammaticNameDisplay] VARCHAR(50),
    [manufacturer] VARCHAR(100),
    [model] VARCHAR(100),
    [modelqualifier] VARCHAR(100),
    [openings] NVARCHAR(MAX)'$.openings' AS JSON
)
OUTER APPLY OPENJSON(openings)
WITH (
    [ProgrammaticName] VARCHAR(100) N'$.programmaticName',
    [Type] VARCHAR(100) N'$.type',
    [Position] VARCHAR(100) N'$.side',
    [Label] VARCHAR(30) N'$.label',
    [ReceptacleType] VARCHAR(100) N'$.receptacleType',
    [ConnectorType] VARCHAR(50) N'$.connectorType',
    [NumberOfLegs] INT N'$.numberOfLegs',
    JSON_QUERY([openings], N'$.legs') AS Legs 
)

实现代码 #

原有逻辑中JSON_QUERY返回的是JSON数组原生格式，只需追加一层数组拆解+字符串聚合即可得到目标格式的逗号分隔字符串。

SQL Server 2017及以上版本（支持STRING_AGG） #

直接使用内置STRING_AGG函数聚合数组元素，修改后完整代码如下：

DECLARE @JSONRoot VARCHAR(50)
SET @JSONRoot = '$._embedded.symbols'
SELECT     
    [id],
    [description],
    [displayCategoryProgrammaticName],
    [displayCategoryProgrammaticNameDisplay],
    [manufacturer],
    [model],
    [modelqualifier],
    [ProgrammaticName],
    [Type],
    [Position],
    [Label],
    [ReceptacleType],
    [ConnectorType],
    [NumberOfLegs],
    legAgg.Legs
FROM OPENROWSET (BULK 'D:\Test\TestFileSymbols.json', SINGLE_CLOB) as j
CROSS APPLY OPENJSON(BulkColumn, @JSONRoot)
WITH
(
    [id] UNIQUEIDENTIFIER,
    [description] VARCHAR(200),
    [displayCategoryProgrammaticName] VARCHAR(50),
    [displayCategoryProgrammaticNameDisplay] VARCHAR(50),
    [manufacturer] VARCHAR(100),
    [model] VARCHAR(100),
    [modelqualifier] VARCHAR(100),
    [openings] NVARCHAR(MAX) '$.openings' AS JSON
)
OUTER APPLY OPENJSON(openings)
WITH (
    [ProgrammaticName] VARCHAR(100) N'$.programmaticName',
    [Type] VARCHAR(100) N'$.type',
    [Position] VARCHAR(100) N'$.side',
    [Label] VARCHAR(30) N'$.label',
    [ReceptacleType] VARCHAR(100) N'$.receptacleType',
    [ConnectorType] VARCHAR(50) N'$.connectorType',
    [NumberOfLegs] INT N'$.numberOfLegs',
    [legsJson] NVARCHAR(MAX) N'$.legs' AS JSON
)
OUTER APPLY (
    SELECT STRING_AGG(value, ',') AS Legs
    FROM OPENJSON(legsJson)
) legAgg

SQL Server 2016及更早版本（无STRING_AGG） #

使用FOR XML PATH方式实现字符串拼接，将上述代码最后一层OUTER APPLY替换为以下片段即可：

OUTER APPLY (
    SELECT STUFF(
        (SELECT ',' + value 
         FROM OPENJSON(legsJson)
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 1, ''
    ) AS Legs
) legAgg

代码优化点：原逻辑中OPENJSON(BulkColumn, ''+@JSONRoot+'')的冗余字符串拼接可直接简化为OPENJSON(BulkColumn, @JSONRoot)，执行效果完全一致。

内容的提问来源于stack exchange，提问作者Gary P.