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?

Do you want to parse the `"TypeDesc"` part of the input JSON? — Zhorov, Oct 23 '20 at 05:48
Hi @Zhorov -- yes .. There can be 15 or 20 such arrays so the output should reflect all of them. The resultset shown here is just parsing of the one array — , Oct 23 '20 at 05:49
Why do that in the *database*? Parse it before storing it and use proper tables to store what looks like lookup data. Or use OPENJSON to parse the data once and store it in lookup tables. — Panagiotis Kanavos, Oct 23 '20 at 05:54

Zhorov · Accepted Answer · 2020-10-23T05:56:32.050

0

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

edited Oct 23 '20 at 05:56

answered Oct 23 '20 at 05:50

Zhorov

28,486
6
27
52

1

Better yet, parse it on the client and store the data in *two* lookup tables. Or use this query to create proper lookup tables. This query can't use any indexes – Panagiotis Kanavos Oct 23 '20 at 05:54

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

1 Answers1