How to parse a json string to flat rows in SQL Server?

Question

I have data like below in a file which I read into a variable in SQL Server:

{
  "TypeCode": [
    {
      "DestinationValue": "Private",
      "SourceValue": "1"
    },
    {
      "DestinationValue": "Public",
      "SourceValue": "2"
    }
],
"TypeDesc": [
    {
      "DestinationValue": "Hello",
      "SourceValue": "1"
    },
    {
      "DestinationValue": "Bye",
      "SourceValue": "2"
    }
]
}

Now I need to convert this into data like below:

Name             SourceValue       DestValue
--------------------------------------------
TypeCode         1                 Hello
TypeCode         2                 Bye

Concern is - there can be many such arrays in this JSON and code should be automatically able to handle all of them without changing the code.

How can we achieve this?

Answer 1

You need to parse the input JSON using OPENJSON() twice (with default and explicit schema):

SELECT j1.[key] AS Name, j2.SourceValue, j2.DestinationValue
FROM OPENJSON(@json) j1
OUTER APPLY OPENJSON(j1.[value]) WITH (
   SourceValue nvarchar(100) '$.SourceValue',
   DestinationValue nvarchar(100) '$.DestinationValue'
) j2

Result:

Name        SourceValue DestinationValue
TypeCode    1           Private
TypeCode    2           Public
TypeDesc    1           Hello
TypeDesc    2           Bye