SQL Server中如何查询数组嵌套结构的JSON文件并提取目标列数据

问题根因 #

你的查询仅返回第一行的核心原因是原JSON的根节点为数组结构，你直接使用'$.data_json'作为根路径解析，无法匹配到外层数组下每个元素的data_json字段，所以只能读到第一个有效节点。

修正后的查询语句 #

无需修改原JSON文件，直接使用以下查询即可得到符合要求的两行结果：

DECLARE @WP_ACCOUNT VARCHAR(MAX)

SELECT @WP_ACCOUNT = BulkColumn
FROM OPENROWSET(BULK'C:\Users\taariq\Desktop\JSON\19-276.json', SINGLE_BLOB) JSON

SELECT 
    j2.Site,
    j2.Month,
    h1.[Grid Electricity],
    h1.[PV Electricity]
FROM OPENJSON(@WP_ACCOUNT) j1 -- 第一层遍历外层根数组
OUTER APPLY OPENJSON(j1.[value], '$.data_json') -- 解析每个数组元素下的data_json字段
WITH (
    Site nvarchar(50) '$.site',
    Month nvarchar(50) '$.month',
    total_kWh nvarchar(max) '$.total_kWh' AS JSON
) j2
CROSS APPLY OPENJSON(j2.total_kWh) 
WITH (
    [Grid Electricity] decimal(18,6) '$.grid_electricity',
    [PV Electricity] decimal(18,6) '$.pv_electricity'
) h1

补充说明 #

• 代码中用电量字段使用decimal类型存储，比字符串类型更适配后续计算、统计的使用需求，如需字符串格式可自行修改为nvarchar类型。

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