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:

SQL SERVER - 2016 - Opening JSON with OPENJSON() openjson-01

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')

SQL SERVER - 2016 - Opening JSON with OPENJSON() openjson-02

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)

JSON
Previous Post
SQL SERVER – Could Not Register Service Principal Name Persists – Notes from the Field #105
Next Post
SQL SERVER – Resource Governor and Database Mapping

Related Posts

11 Comments. Leave new

  • 2nd code sample doesnt match with the result….

    Reply
  • Hi Pinal,
    I had been always big fan of your blogs.
    Very much simplified and easy to understand.
    Thanks a lot for your dedication towards community.

    Reply
  • Milligan, Adam
    December 4, 2015 8:33 pm

    It looks like you are using the exact same SQL statement in both examples, but the results are different. Is this a mistake or did I miss something?

    Reply
  • Hi Pinal,
    2nd code seems to not correct. Please modify this. :)

    Reply
  • can jsonExpression be a path like(C:test\test.json)?

    OPENJSON( jsonExpression [ , path ] )
    [
    WITH (
    colName type [ column_path ] [ AS JSON ]
    [ , colName type [ column_path ] [ AS JSON ] ]
    [ , . . . n ]
    )
    ]

    Reply
  • I have same type of data but its available in the json file contain 3000 rows, how can i process the same, i need the results as table key should be in column and values should in row with 2nd table results

    Reply
  • Hi Pinal, thank you for you articles, it has helped me a lot…

    I however have a question for you. It is easy using the OPENJSON function when you have JSON data stored in a single parameter. but what if i have multiple rows of JSON files stored (same structure) in a table. How would i query that when using OPENJSON and building a structured table from that.

    Please let me know if you require additional explanation of what i’m trying to do….

    Hanri (South Africa)

    Reply
  • Michael Sultana
    May 18, 2017 1:42 pm

    Hi,
    Is there any way where you can specify the path as variable in open json like the below

    DECLARE @counter int
    DECLARE @expression NVARCHAR(50)
    set @counter = 0
    set @expression = ‘$.amounts[‘+CONVERT(NVARCHAR(10),@counter)+’]’

    SELECT *
    FROM OPENJSON(@JSON,@expression)
    WITH(Charge_Id int, Amount money, FineType_Id int, user_id int)AS JSON

    Since this causes an error

    Thanks for your help

    Reply
  • Hello Pinal I dont have SQL SERVER 2016 so how to use this OPENJSON PLs Help

    Reply
  • Hi Pinal, how do I retrieve internal “key” (sortorder) column from OpenJSON while using WITH clause similar to @mp:id in OpenXml?

    Reply

Leave a Reply