How to return a table from JSON Object in SQL Server 2016

   

     OpenJson is a table-valued function in SQL Server 2016 which parses JSON text and returns the objects and properties of JSON text as a table. Since it returns a table, you can use it in FROM clause like any other table.

    To be able to use OpenJson function, your database compatibility level has to be 130 or higher. Otherwise SQL Server can not find/run OpenJson function. You can adjust the compatibility level with the following code. It's always good to check with your DBA first if this might be an issue in production before you start to develop with this function.

ALTER DATABASE DbaseName SET COMPATIBILITY_LEVEL = 130

    Let's look at the OpenJson Syntax now.

OPENJSON (jsonExp [,path]) [<with_clause>] )

     Let's ignore the with clause for now, and focus on the main functionality first. OPENJSON function parses the given json expression and It returns parsed object or objects as a table. Before we go in more details, Here is the JSON object I am going to use in my demos.

declare @jsonVal nvarchar(max) = '
  {
     "Product":{
      "ProductId":1,"Name":"60W Light Bulb","Price":"10.00",
      "Alike": { "AlikeProducts" : ["2"]}},
    "Product":{
      "ProductId":2,"Name":"100W Light Bulb","Price":"7.00"},
    "Product":{
     "ProductId":3,"Name":"HDMI Cable","Price":"7.00"},
    "Product":{
     "ProductId":4,"Name":"USB Cable","Price":"2.50"},
    "Product":{
      "ProductId":5,"Name":"Power Cable","Price":"5.00"},
    "Product":{
      "ProductId":6,"Name":"Monitor","Price":"160.00"},
    "Product":{
      "ProductId":7,"Name":"Keyboard","Price":"27.00"}
   }
'

When I call the OPENJSON function without any path, SQL Server returns the following result.

     So far so good, I am sure you are wondering what is the type column for. Type column is an integer and it represents the type of the object.

1. String
2. Integer
3. Boolean
4. JSON Array
5. JSON Object

     Now I want to use the path to filter the Json text. I have a sub-object under the Product 1. It contains the list of alike products for the product.

SELECT * FROM OPENJSON(@jsonVal,'$.Product.Alike."AlikeProducts"')

This returns the AlikeProducts of all JSON objects. Since I have

Now, Let's look at the WITH-Clause.

<with-clause> = WITH ( {colName type [ columnPath ] [ AS JSON ] } [,...n])

With clause defines the output schema for the OPENJSON function to return.Here is the parameters and how to use them in the clause.

colName : Name for the OUTPUT column. By Default OPENJSON uses the name of the column to match a property in the JSON object.

type: This is the data type for the OUTPUT column.

column_path : This is the JSON path which specifies the location of a column in JSON object.

AS JSON : If you use this option, returned result will be in JSON format otherwise returned result will be a scalar value. To use this option, your return object type must be NVARCHAR(MAX)

SELECT * FROM OPENJSON(@jsonVal,'$.Product')
 WITH (
      ProductId int '$.ProductId',
      Name varchar(50) '$.Name',
      Price money '$.Price',            
      Alike  nvarchar(MAX)  AS JSON
 )

Now, Here is a great tip for querying the JSON in SQL Server. JSON_VALUE function extracts the value/s from JSON. For example to get the Products with ProductId 1, we can run the following query.

SELECT * FROM OPENJSON(@jsonVal)
WHERE JSON_VALUE(value,'$.ProductId')=1

We are parsing the JSON twice in this query, and we can dramatically improve the performance here by using the WITH clause of OPENJSON. Let's look at that.

SELECT * FROM OPENJSON(@jsonVal)
 WITH (
      ProductId int '$.Product.ProductId',
  Product NVARCHAR(MAX) AS JSON
 ) WHERE ProductId = 1

    In the previous example, I am parsing the JSON object once, then query the parsed result to get Product I am looking for.