选择"格式类似于使用 T-SQL 的表报表的 Json"

Question

我以以下格式将JSON存储在SQL Server数据库表中。我已经能够捏造一种方法来获取我需要的值，但觉得一定有更好的方法来使用 T-SQL 来实现它。JSON 从以下格式的报表输出，其中"列"中的列名对应于"行"-"数据"数组值。

因此，"财政月份"列对应于数据值"11"，"财政年度"到"2019"等。

{
"report": "Property ETL",
"id": 2648,
"columns": [
{
"name": "Fiscal Month",
"dataType": "int"
},
{
"name": "Fiscal Year",
"dataType": "int"
},
{
"name": "Portfolio",
"dataType": "varchar(50)"
},
{
"name": "Rent",
"dataType": "int"
}
],
"rows": [
{
"rowName": "1",
"type": "Detail",
"data": [
11,
2019,
"West Group",
10
]
},
{
"rowName": "2",
"type": "Detail",
"data": [
11,
2019,
"East Group",
10
]
},
{
"rowName": "3",
"type": "Detail",
"data": [
11,
2019,
"East Group",
10
]
},
{
"rowName": "Totals: ",
"type": "Total",
"data": [
null,
null,
null,
30
]
}
]
}    

为了获取"data"数组中的数据，我目前在 T-SQL 中有一个 2 步过程，我在其中创建一个临时表，并从"$"插入行键/值。行'在那里。然后我可以为每行选择单独的列

CREATE TABLE #TempData 
(
Id INT,
JsonData VARCHAR(MAX)
)
DECLARE @json VARCHAR(MAX);
DECLARE @LineageKey INT;
SET @json = (SELECT JsonString FROM Stage.Report);
SET @LineageKey = (SELECT LineageKey FROM Stage.Report);    
INSERT INTO #TempData(Id, JsonData)
(SELECT [key], value FROM OPENJSON(@json, '$.rows'))
MERGE [dbo].[DestinationTable] TARGET
USING 
(
SELECT 
JSON_VALUE(JsonData, '$.data[0]') AS FiscalMonth,
JSON_VALUE(JsonData, '$.data[1]') AS FiscalYear,
JSON_VALUE(JsonData, '$.data[2]') AS Portfolio,
JSON_VALUE(JsonData, '$.data[3]') AS Rent
FROM #TempData
WHERE JSON_VALUE(JsonData, '$.data[0]') is not null
) AS SOURCE     
... 
etc., etc.

这有效，但我想知道是否有办法直接选择数据值，而无需将其放入临时表中的中间步骤。我读过的文档和示例似乎都要求数据具有与之关联的名称才能访问它。当我尝试直接按索引在某个位置访问数据时，我只会得到 Null。

Answer 1

我希望我正确理解你的问题。如果您知道列名，则需要使用显式模式进行一次OPENJSON()调用，但如果要从$.columns中读取 JSON 结构，则需要动态语句。

杰森：

DECLARE @json nvarchar(max) = N'{
"report": "Property ETL",
"id": 2648,
"columns": [
{
"name": "Fiscal Month",
"dataType": "int"
},
{
"name": "Fiscal Year",
"dataType": "int"
},
{
"name": "Portfolio",
"dataType": "varchar(50)"
},
{
"name": "Rent",
"dataType": "int"
}
],
"rows": [
{
"rowName": "1",
"type": "Detail",
"data": [
11,
2019,
"West Group",
10
]
},
{
"rowName": "2",
"type": "Detail",
"data": [
11,
2019,
"East Group",
10
]
},
{
"rowName": "3",
"type": "Detail",
"data": [
11,
2019,
"East Group",
10
]
},
{
"rowName": "Totals: ",
"type": "Total",
"data": [
null,
null,
null,
30
]
}
]
}'

固定结构声明：

SELECT *
FROM OPENJSON(@json, '$.rows') WITH (
[Fiscal Month] int '$.data[0]',
[Fiscal Year] int '$.data[1]',
[Portfolio] varchar(50) '$.data[2]',
[Rent] int '$.data[3]'
)

动态语句：

DECLARE @stm nvarchar(max) = N''
SELECT @stm = CONCAT(
@stm,
N',',
QUOTENAME(j2.name),
N' ',
j2.dataType,
N' ''$.data[',
j1.[key],
N']'''
)
FROM OPENJSON(@json, '$.columns') j1
CROSS APPLY OPENJSON(j1.value) WITH (
name varchar(50) '$.name',
dataType varchar(50) '$.dataType'
) j2
SELECT @stm = CONCAT(
N'SELECT * FROM OPENJSON(@json, ''$.rows'') WITH (',
STUFF(@stm, 1, 1, N''),
N')'
)
PRINT @stm
EXEC sp_executesql @stm, N'@json nvarchar(max)', @json

结果：

--------------------------------------------
Fiscal Month    Fiscal Year Portfolio   Rent
--------------------------------------------
11              2019        West Group  10
11              2019        East Group  10
11              2019        East Group  10
30 

Answer 2

是的，没有临时表是可能的：

DECLARE @json NVARCHAR(MAX) = 
N'
{
"report": "Property ETL",
"id": 2648,
"columns": [
{
"name": "Fiscal Month",
"dataType": "int"
},
{
"name": "Fiscal Year",
"dataType": "int"
},
{
"name": "Portfolio",
"dataType": "varchar(50)"
},
{
"name": "Rent",
"dataType": "int"
}
],
"rows": [
{
"rowName": "1",
"type": "Detail",
"data": [
11,
2019,
"West Group",
10
]
},
{
"rowName": "2",
"type": "Detail",
"data": [
11,
2019,
"East Group",
10
]
},
{
"rowName": "3",
"type": "Detail",
"data": [
11,
2019,
"East Group",
10
]
},
{
"rowName": "Totals: ",
"type": "Total",
"data": [
null,
null,
null,
30
]
}
]
}    
}';

和查询：

SELECT s.value,
rowName = JSON_VALUE(s.value, '$.rowName'),
[type] = JSON_VALUE(s.value, '$.type'),
s2.[key],
s2.value
FROM OPENJSON(JSON_QUERY(@json, '$.rows')) s
CROSS APPLY OPENJSON(JSON_QUERY(s.value, '$.data')) s2;

数据库<>小提琴演示

或者作为每个详细信息的单行：

SELECT s.value,
rowName = JSON_VALUE(s.value, '$.rowName'),
[type] = JSON_VALUE(s.value, '$.type'),
JSON_VALUE(s.value, '$.data[0]') AS FiscalMonth,
JSON_VALUE(s.value, '$.data[1]') AS FiscalYear,
JSON_VALUE(s.value, '$.data[2]') AS Portfolio,
JSON_VALUE(s.value, '$.data[3]') AS Rent
FROM OPENJSON(JSON_QUERY(@json, '$.rows')) s;

DB<>小提琴演示2