SQL SERVER – 2016 – Opening JSON with OPENJSON()

The first blog I wrote about JSON with SQL Server 2016 was to get a valid JSON output from relational tables. You can read that again from: SQL SERVER – Getting started and creating JSON using SQL Server 2016. In this blog, we will try to get to the next stage of reading JSON document.

One of the most important functions will provide the ability to parse JSON text and return properties as a relational table using OPENJSON built-in table value function. OPENJSON may have explicitly defined schema of results or default schema will be used. We will try to use the default schema in this blog to start with. OPENJSON provides a rowset view over a JSON document. Because OPENJSON is a rowset provider, OPENJSON can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.

Let us look at a simple example to use OPENJSON:

SELECT * FROM OPENJSON('["India", "United Kingdom", "United States", "Mexico", "Singapore"]')
GO


The output looks like:

OPENJSON will parse JSON text, and for each element in the array will generate one row with an index and value. If json Expression contains an object, (key, value) pairs will be properties on the first level is sent out (look at tags from below example). In the below example, we will show a complex JSON text:

DECLARE @json NVARCHAR(4000)
SET @json = N'{
"id": 1,
"name": "A green door",
"price": 12.50,
"color": "Green",
"type": "bricks",
"tags": ["home", "green"] }'
SELECT * FROM OPENJSON(@json) AS MyJSON;
SELECT * FROM OPENJSON(@json, N'lax $.tags')


It is interesting to see how we can get values from a complex JSON object using both the queries. Although OPENXML is used as a template for OPENJSON and the usage looks similar, there are fundamental differences:

• OPENJSON accepts text as an input while OPENXML accepts handle. Drawback of OPENJSON approach is that two same JSON texts provided to different OPENJSON calls must be parsed each time.
• OPENXML without explicit schema parses entire XML tree and returns flattened table with all nodes. OPENJSON returns rows with (key, value) schema.

Now that we have got the basics covered, in future posts will try to look at advanced usage of OPENJSON construct. Do let me know if you have started playing around with SQL Server 2016 already?

Reference: Pinal Dave (https://blog.sqlauthority.com)