将Json转换为Sql Server中的表

Question

我有以下Json:

{
"archeTypes": [
{
"archeTypeId": 12,
"elements": [
{
"elementId": 6,
"value": "string6"
},
{
"elementId": 7,
"value": "string7"
}
]
}
]
}

下面是我的代码:

DECLARE @json NVARCHAR(MAX);
SET @json='{"archeTypes":[{"archeTypeId":12,"elements":[{"elementId":6,"value":"string6"},{"elementId":7,"value":"string7"}]}]}';
SELECT *
FROM OPENJSON(@json)
WITH (
ArcheTypeId INT '$.archeTypes.archeTypeId',
ElementId INT '$.archeTypes.elements.elementId',
Value NVARCHAR(max) '$.archeTypes.elements.value'
);

这是我想要的表:

string6string7

这有帮助吗?(使用cross apply)

DECLARE @json NVARCHAR(MAX);
SET @json='{"archeTypes":[{"archeTypeId":12,"elements":[{"elementId":6,"value":"string6"},{"elementId":7,"value":"string7"}]}]}';
SELECT archeTypes.ArcheTypeId,elements.ElementId,elements.Value
FROM OPENJSON(@json)
WITH 
(
archeTypes nvarchar(max) as JSON
) as archeType cross apply
OPENJSON(archeType.archeTypes)
WITH
(
archeTypeId INT,
elements nvarchar(max) as JSON
) as archeTypes cross apply
OPENJSON(archeTypes.elements)
WITH
(
elementId INT,
value nvarchar(max)
) as elements
;

,DB&lt的在小提琴

您也可以为OPENJSON提供一个JSON路径，以便直接跳转到archetypes

DECLARE @json nvarchar(max) = N'{"archeTypes":[{"archeTypeId":12,"elements":[{"elementId":6,"value":"string6"},{"elementId":7,"value":"string7"}]}]}';
SELECT
at.ArcheTypeId,
e.ElementId,
e.Value
FROM OPENJSON(@json, '$.archeTypes')
WITH (
archeTypeId INT,
elements nvarchar(max) AS JSON
) AS at
CROSS APPLY OPENJSON(at.elements)
WITH (
elementId INT,
value nvarchar(100)
) AS e;

,db&lt的在小提琴