Sunday, May 21, 2017

SELECT query on OpenJSON using SQL Server 2016


OpenJSON converts an array of objects in a variable in JSON Format to a rowset
that can be queried with standard SQL Select statement.

Here is an example:

We are going to look at a JSON list of my first batch of students who took my course shown here. 

["wclass",
{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}
},
{"student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
},
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}
}]

This is the result of running OpenJSON using the above:



Now you can run a SELECT query with a with clause on the rows returned by OpenJSON as shown here:

The first member "wclass" has nulls for the selected columns. It exists because it actually was in the original XML that got converted to JSON.
Here are my more recent JSON related articles:

JSON validation in SQL Server:
https://hodentekmsss.blogspot.com/2016/11/using-json-validator-in-sql-server.html

Nested JSON using SQL Server 2012:
https://hodentekmsss.blogspot.com/search?q=json

Retrieve JSON formatted data from SQL Anywhere 17
https://hodentekmsss.blogspot.com/2016/11/retrieve-data-from-sql-anywhere-17-in.html

No comments:

Post a Comment

Do you know what you installed while custom installing SQL Server 2022?

 When you install any software in Windows OS, the key information gets into the Registry and some of which you can look up in the control pa...