SQL Server 2016: JSON, Part 2 – Data Generation

In my previous publication, we have explored how you can transfer data from JSON to SQL Server 2016. Now, I am going to describe how you can generate JSON from relational data. You can do this using the FOR JSON statement.

The easiest way is to use FOR JSON AUTO. In this case, it will generate the JSON object array where each row in the selection will be a separate object, while a column will be a property.

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

As you can see, NULL values are being ignored. If we want to include them into JSON, we can use the additional INCLUDE_NULL_VALUES option.

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”,”dt”:null},

{“id”:3,”name”:null,”dt”:”2000-01-01T00:00:00″}]

The WITHOUT_ARRAY_WRAPPER statement may help if we want to display JSON without square brackets.

{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}

If we need to combine the results with the root component, then it is better to use the ROOT statement and specify its proper name.

{“root”:

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

}

Finally, if you want to create JSON with a more complex structure, specify proper names for properties and group them, then you should use the FOR JSON PATH statement. I recommend that you should refer to the first article about JSON to learn more about the path statements for JSON.

To check how you can generate JSON with a more complex structure, run this query:

[{“id”:1,”data”:{“full_name”:”qwe”,”add date”:”1900-01-01T00:00:00″}},

{“id”:2,”data”:{“full_name”:”asd”}},

{“id”:3,”data”:{“add date”:”2000-01-01T00:00:00″}}]

In my further publication, we will explore how you can convert the JSON object into a relational data set.

Sergey Olontsev

Sergey Olontsev

Software Expert at Kaspersky Lab
Sergey has been working with SQL Server for more than a decade as a database administrator, developer, consultant, and trainer, focusing on high availability and disaster recovery solutions, ETL, developing high-performance solutions, troubleshooting and performance tuning. He is an owner of prestigious Microsoft Certified Master certification on SQL Server and MVP Data Platform award and regularly speaks at various SQL Server user group meetings, SQL Saturday events and other conferences worldwide.
Sergey Olontsev

Sergey Olontsev

Sergey has been working with SQL Server for more than a decade as a database administrator, developer, consultant, and trainer, focusing on high availability and disaster recovery solutions, ETL, developing high-performance solutions, troubleshooting and performance tuning. He is an owner of prestigious Microsoft Certified Master certification on SQL Server and MVP Data Platform award and regularly speaks at various SQL Server user group meetings, SQL Saturday events and other conferences worldwide.