Dynamic JSON Parsing using T-SQL

Question

I have a highly-unstructured JSON string, coming as a response of calling a REST API:

{
    "info": "Test Json Structure",
    "Owner": "Self-Owned",
    "managedObjects": [{
            "Name": "Device1",
            "Class": "A"
        }, {
            "Name": "Device2",
            "Class_145": "Ax01",
            "Class_11": "B",
            "Type_125478": {
                "Model": "1",
                "Manufacturer": "External"
            },
            "Type_SD": {
                "Model": "00",
                "Manufacturer": "Internal"
                }
        }, {
            "Name": "Device3",
            "Class_x": "Cx11",
            "Class_T": "C8Y",
            "Type": {
                "Model": "1x",
                "Manufacturer": "Internal"
            }
        }
    ]
}

How can I dynamically parse this object using T-SQL, so that all child elements to represent columns of a table? More importantly, how to deal with Type, Type_125478, Type_SD objects, where in fact they have the same structure (Model, Manufacturer) but some how their names are different. Also keep in mind that the device may send a new identifier (Type_XYZ), which didn't exist before, but luckily has the same structure (Model, Manufacturer).

So each time you would have a different number of columns as a result of the query, depending on whether you get many `"Class_145"`, `"Class_11"`s or not? — GSerg, Oct 21 '20 at 14:43
I think it's better to have such types as type_name and type_value instead of many columns since you can have only fixed layout of resultset — astentx, Oct 21 '20 at 14:46
@GSerg one solution to this is to create the full set of columns, and columns which match the json will have values, others will have NULL .. this is what R does when parsing such dynamic string. The question is how to do the same in T-SQL — khidir, Oct 21 '20 at 15:22
@astentx that would simplify the problem for sure, but I have no control over the source in the way it sends the data — khidir, Oct 21 '20 at 15:23
Which version of SQL-Server? JSON support was introduced with v2016. — Shnugo, Oct 21 '20 at 16:16
@khidir What will you do with such multivalue relation as in object 2. There is two classes and two types, should this be different columns? — astentx, Oct 21 '20 at 16:20

Shnugo · Accepted Answer · 2020-10-21T16:38:21.923

You might use something like this to explode the whole lot into a tabular structure and proceed with this (needs a SQL-Server version v2016+):

DECLARE @YourJSON NVARCHAR(MAX)=
N'{
    "info": "Test Json Structure",
    "Owner": "Self-Owned",
    "managedObjects": [{
            "Name": "Device1",
            "Class": "A"
        }, {
            "Name": "Device2",
            "Class_145": "Ax01",
            "Class_11": "B",
            "Type_125478": {
                "Model": "1",
                "Manufacturer": "External"
            },
            "Type_SD": {
                "Model": "00",
                "Manufacturer": "Internal"
                }
        }, {
            "Name": "Device3",
            "Class_x": "Cx11",
            "Class_T": "C8Y",
            "Type": {
                "Model": "1x",
                "Manufacturer": "Internal"
            }
        }
    ]
}';

--the query

SELECT A.info
      ,A.[Owner]
      ,C.[key] AS TagName
      ,CASE WHEN D.Model IS NULL THEN C.[value] END AS TagValue 
      ,D.Model
      ,D.Manufacturer
FROM OPENJSON(@YourJSON)
WITH(info NVARCHAR(MAX)
    ,[Owner] NVARCHAR(MAX)
    ,managedObjects NVARCHAR(MAX) AS JSON) A
OUTER APPLY OPENJSON(A.managedObjects) B
OUTER APPLY OPENJSON(B.[value]) C
OUTER APPLY OPENJSON(CASE WHEN ISJSON(C.[value])=1 THEN C.[value] END) 
WITH (Model NVARCHAR(MAX)
     ,Manufacturer NVARCHAR(MAX))D;

--the result

+---------------------+------------+-------------+----------+-------+--------------+
| info                | Owner      | TagName     | TagValue | Model | Manufacturer |
+---------------------+------------+-------------+----------+-------+--------------+
| Test Json Structure | Self-Owned | Name        | Device1  |       |              |
+---------------------+------------+-------------+----------+-------+--------------+
| Test Json Structure | Self-Owned | Class       | A        |       |              |
+---------------------+------------+-------------+----------+-------+--------------+
| Test Json Structure | Self-Owned | Name        | Device2  |       |              |
+---------------------+------------+-------------+----------+-------+--------------+
| Test Json Structure | Self-Owned | Class_145   | Ax01     |       |              |
+---------------------+------------+-------------+----------+-------+--------------+
| Test Json Structure | Self-Owned | Class_11    | B        |       |              |
+---------------------+------------+-------------+----------+-------+--------------+
| Test Json Structure | Self-Owned | Type_125478 |          | 1     | External     |
+---------------------+------------+-------------+----------+-------+--------------+
| Test Json Structure | Self-Owned | Type_SD     |          | 00    | Internal     |
+---------------------+------------+-------------+----------+-------+--------------+
| Test Json Structure | Self-Owned | Name        | Device3  |       |              |
+---------------------+------------+-------------+----------+-------+--------------+
| Test Json Structure | Self-Owned | Class_x     | Cx11     |       |              |
+---------------------+------------+-------------+----------+-------+--------------+
| Test Json Structure | Self-Owned | Class_T     | C8Y      |       |              |
+---------------------+------------+-------------+----------+-------+--------------+
| Test Json Structure | Self-Owned | Type        |          | 1x    | Internal     |
+---------------------+------------+-------------+----------+-------+--------------+

Hint: You might add B.[key] to the result as an object identifier.

The idea in short

We use a first OPENJSON to get into your JSON. The WITH-clause allows to address the JSON-props as columns. We return the managedObejcts as JSON themselve.
We use one more OPENJSON to dive into the managed objects.
This will return an array of objects. We can pass the value into another OPENJSON.
Whenever the value can be interpreted as JSON on its own, we use one more OPENJSON, this time with a WITH-clause again to get the internal props as columns.

You can insert this result into a table (declared, temp, physical...) and continue with this easy-to-read set.

Thanks @Shnugo that actually worked, as I am using SQL Server 2017.. — khidir, Oct 22 '20 at 14:25

Dynamic JSON Parsing using T-SQL

1 Answers1