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).

Answer 1

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.