Convert Json to table in Sql Server

Question

I have the following Json:

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

Here is my code:

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'
    );

Here is the table that I want to:

ArcheTypeId	ElementId	Value
12	6	string6
12	7	string7

How can I convert the json to table?

Answer 1

You can also supply a JSON path to OPENJSON in order to jump directly to 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<>fiddle

Answer 2

Does this help? (using 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<>Fiddle