Querying JSON and its nested objects with TSQL

When we were faced to store lot of dynamically modelled data in a relational DB, we decided on storing them in a JSON column of SQL server.

The well documented recourses came in handy during initial development.

Although with some workaround

• Runtime JSON deserialization
• Getting around with Store procedures while whole application runs with entity framework

Until today I never got to query nested arrays of a JSON object with SQL. That is when I stumbled upon another method in T-SQL called OPENJSON

OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns.

In other words, OPENJSON provides a rowset view over a JSON document. You can explicitly specify the columns in the rowset and the JSON property paths used to populate the columns. Since OPENJSON returns a set of rows, you can use OPENJSON in the FROM clause of a T-SQL statement just as you can use any other table, view, or table-valued function.

Few samples

Consider this sample json data

DECLARE @json NVARCHAR(MAX) = '{
    "firstName":"James",
    "lastName":"Doe",
    "ChildrenDetails":[
        {
            "Id":1,
            "Name":"John Doe",
            "Languages":["English", "German"]
        },
        {
            "Id":2,
            "Name":"Jane Doe",
            "Languages":["English"]
        },
        {
            "Id":3,
            "Name":"June Doe",
            "Languages":["German", "Tamil"]
        }]
}'

1. Querying into arrays (ChildrenDetails)

SELECT 
    JSON_VALUE(d.value,'$.Id') AS Id,
    JSON_VALUE(d.value,'$.Name') AS Name
FROM OPENJSON(@json,'$.ChildrenDetails') AS d

2. Flatten Nested arrays (ChildrenDetails + each language)

SELECT 
JSON_VALUE(d.value,'$.Id') AS Id,
JSON_VALUE(d.value,'$.Name') AS Languages,
l.value AS Name
  FROM OPENJSON(@json,'$.ChildrenDetails') AS d CROSS APPLY OPENJSON (d.value,'$.Languages') AS l

Hopefully you can try this with complex JSON structures. The use cases are many, but today this came in handy for a feature to us 😃

Before you leave, a meme:

EOF