I have a problem with JSON data and how to get it in SQL table so the data is:

[
   {
      "id":"1121131",
      "idGroups":[
         "123",
         "X999"
      ],
      "idGroupNames":[
         "Neutral",
         "Service"
      ]
   },
   {
      "id":"2233154",
      "idGroups":[
         "654321"
      ],
      "idGroupNames":[
         "Position"
      ]
   }
]

and the desired output is

I have trying to get the desired result with OPENJSON() and CROSS APPLY, but I think that I'm not getting anywhere.

my original attempt was

DECLARE @json NVARCHAR(MAX) = N'[
   {
      "id":"1121131",
      "idGroups":[
         "123",
         "X999"
      ],
      "idGroupNames":[
         "Neutral",
         "Service"
      ]
   },
   {
      "id":"2233154",
      "idGroups":[
         "654321"
      ],
      "idGroupNames":[
         "Position"
      ]
   }
]'
SELECT id,idGroup,idGroupName FROM OPENJSON (@json)
WITH (
    id INT 'strict $.id',
    idGroups NVARCHAR(MAX) '$.idGroups' AS JSON,
    idGroupNames NVARCHAR(MAX) '$.idGroupNames' AS JSON
    ) CROSS APPLY OPENJSON(idGroups)
    WITH (
        idGroup VARCHAR(500) '$'
    ) CROSS APPLY OPENJSON(idGroupNames)
    WITH (
        idGroupName VARCHAR(500) '$'
    )

CodePudding user response：

You need to use OPENJSON() with default schema and two additional APPLY operators. The following statement is a possible solution to your problem:

JSON:

DECLARE @json nvarchar(max) = N'[
   {
      "id":"1121131",
      "idGroups":[
         "123",
         "X999"
      ],
      "idGroupNames":[
         "Neutral",
         "Service"
      ]
   },
   {
      "id":"2233154",
      "idGroups":[
         "654321"
      ],
      "idGroupNames":[
         "Position"
      ]
   }
]'

Statement:

SELECT j.id, j1.[value] AS idGroups, j2.[value] AS idGroupNames
FROM OPENJSON(@json) WITH (
   id nvarchar(7) '$.id',
   idGroups nvarchar(max) '$.idGroups' AS JSON,
   idGroupNames nvarchar(max) '$.idGroupNames' AS JSON
) j
CROSS APPLY OPENJSON(j.idGroups) j1
CROSS APPLY OPENJSON(j.idGroupNames) j2
WHERE j1.[key] = j2.[key]

CodePudding user response：

You can do it without a third OPENJSON, using JSON_VALUE and a dynamic path.

This only works in SQL Server 2017

DECLARE @json nvarchar(max) = N'[
   {
      "id":"1121131",
      "idGroups":[
         "123",
         "X999"
      ],
      "idGroupNames":[
         "Neutral",
         "Service"
      ]
   },
   {
      "id":"2233154",
      "idGroups":[
         "654321"
      ],
      "idGroupNames":[
         "Position"
      ]
   }
]';

SELECT j.id, j1.[value] AS idGroups, JSON_VALUE(j.idGroupNames, '$['   j1.[key]   ']') AS idGroupNames
FROM OPENJSON(@json) WITH (
   id nvarchar(7) '$.id',
   idGroups nvarchar(max) '$.idGroups' AS JSON,
   idGroupNames nvarchar(max) '$.idGroupNames' AS JSON
) j
CROSS APPLY OPENJSON(j.idGroups) j1;

db<>fiddle