Reading JSON data in T-SQL on SQL Server
Extracting values from JSON string on SQL Server using T-SQL
JSON is the most commonly used data fromat nowdays. It is lighter than XML, more readable and it's sindax complies with most OOP C-Like lnguages such as Java, JavaScript and C#.
There is not really many downsides of using JSON, so that is one of the rasons everyone is using it in one way or another.
Whan it comes to relational database it is a bit different. MIcrosoft SQL server does not use JSON for any data structure and by nature does not understand it. Starting from SQL Server 2016t, Microsoft introduced JSON support by adding OPENJSON function.
OPENJSON function basically transforms JSON document into collection of row-sets, in simple workds output of the function id table representation of JSON data.
DECLARE @json NVARCHAR(4000) = N'{ "FirstName":"John", "LastName":"Smith", "Age": 32, "DOB": "1986-10-07", "Married": false }' SELECT * FROM OPENJSON(@json)
Simple OPENJSON call wothout parameters will produce the output with key/value pair table with additional column of data type of the data read from JSON document.
key | value | type |
FirstName | John | 1 |
LastName | Smith | 1 |
Age | 32 | 2 |
DOB | 1986-10-07 | 1 |
Married | false | 3 |
If you want to format table to get properties as columns which is most usefull way to use table for joining with other data tables, you can explicitly conver tot he type and column name using options
SELECT * FROM OPENJSON(@json) WITH ( FirstName NVARCHAR(50) '$.FirstName', LastName NVARCHAR(100) '$.LastName', Age int '$.Age', DOB date '$.DOB', Married bit '$.Married' )
FirstName | LastName | Age | DOB | Married |
John | Smith | 32 | 1986-10-07 | 0 |
Another problem you might face is mutli-dimensional structure of JSON.
DECLARE @json NVARCHAR(4000) = N'{ "FirstName":"John", "LastName":"Smith", "Age": 32, "DOB": "1986-10-07", "Married": false, "Residence": { "Country":"Spain", "Region":"Andalusia", "City":"Seville" } }' SELECT * FROM OPENJSON(@json) WITH ( FirstName NVARCHAR(50) '$.FirstName', LastName NVARCHAR(100) '$.LastName', Age int '$.Age', DOB date '$.DOB', Married bit '$.Married', Residence VARCHAR(200) '$.Residence' )
You can see that embeded type of Residence property is null in output.
Flat structure is easy to read because it has two dimensions like data table, but if you introduce for example embeded objects as a value of a property, you have to change a query a little bit to adapt it to the JSON structure.
SELECT * FROM OPENJSON(@json) WITH ( FirstName NVARCHAR(50) '$.FirstName', LastName NVARCHAR(100) '$.LastName', Age int '$.Age', DOB date '$.DOB', Married bit '$.Married', ResidenceCountry VARCHAR(200) '$.Residence.Country', ResidenceRegion VARCHAR(200) '$.Residence.Region', ResidenceCity VARCHAR(200) '$.Residence.City' )
FirstName | LastName | Age | DOB | Married | ResidenceCountry | ResidenceRegion | ResidenceCity |
John | Smith | 32 | 1986-10-07 | False | Spain | Andalusia | Seville |
Disclaimer
Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.
Comments for this article