Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

JSON in SQL 2016

DZone's Guide to

JSON in SQL 2016

With the new SQL server, you can have the best of both worlds. In your data models, you can choose when to use traditional structures and when to introduce NoSQL concepts.

Free Resource

The Integration Zone is brought to you in partnership with Cloud Elements. What's below the surface of an API integration? Download The Definitive Guide to API Integrations to start building an API strategy.

This article details the application of JSON data and the use new SQL 2016 constructs that enable an integration of JSON data to a relational schema. The idea behind this article to the list examples of JSON nested elements, sample data converted to JSON, and JSON relational data.

Introduction

JSON stands for JavaScript Object Notation. JSON is the primary data representation for all NoSQL databases. This is a natural fit for developers who use JSON as the data interchange format in their applications. The relative ability of JSON (JSON records are well structured but easily extended) in terms of its scalability has attracted developers looking DB migrations in agile environments. Data and schema, in volume, can be hard to change. Rewriting a large dataset stored on disk while keeping the associated applications online can be time-consuming. It can take days of background processing, in moderate to large examples, to upgrade data.

Background

Most traditional relational database engines now support JSON. With SQL Server 2016, it's easy to interchange JSON data between applications and the database engine. Microsoft has provided various functions and capabilities to parse JSON data. They tried to bring JSON data into relational storage. It also provides an ability to transform relational data into JSON and JSON data into denormalized data.

Having these additional JSON features built into SQL Server should make it easier for applications to exchange JSON data with SQL Server. This functionality provides flexibility in the integration of JSON data into the relational database engine. Developers can write and invent complex queries during their periodic stages of development process.

Relational databases are traditional data storage, constructive and intuitive SQL language, complex query design, and ACID properties. NoSQL offers different concepts; complex structures are placed together into collections of entities where you can take everything you need with one read operation or where you can insert complex structures with a single write-and-follow CAP property.

Relational databases normalize data to some degree; that is, rather than repeating a piece of data in multiple rows, a table that needs that information will store a foreign key pointing to another table that holds the data. On the other hand, this process means that data is typically shredded from its original form to fit into tables and then reassembled at run time by joining tables in response to a query. This becomes particularly expensive as the data set grows and the data needs to be partitioned among multiple database servers.

JSON Syntax Rules

JSON syntax is derived from JavaScript object notation syntax:

  • Data is in name/value pairs.
    • {"key":"value"} – most common format for objects.
  • Data is separated by commas.
    • {"key":"value"},{"key":"value"}.
  • Curly braces hold objects
    • {"key":{"key":"value"}}.
  • Square brackets hold arrays
    • {"key":[ {"key":"value"},{"key":"value"} ]}.

JSON Values

In JSON, values must be one of the following data types:

  • A string.
  • A number.
  • An object (JSON object).
  • An array.
  • A boolean.
  • Null.

Basic Structure

If you have parent/child (fact/dimension) relationships where related child information are not changed frequently and you need to read child records together with the parent without additional JOINS, you can store child records in parent table as JSON array.

In the traditional database, the normalization process ensures the minimizing of the amount of information that duplicates. In NoSQL, it intentionally duplicates it to make it easier to use. Let's think about representing a number of students taking a class. A normalized way of representing data is given below. The use of arrays denotes the dimension data of the relational table:

{
course: "Basic programming",
room: "1A",
   students: [
                     {
                        id: 1,
                        name: "Prashanth"
                      },
                       {
                         id: 2,
                         name: "Jayaram"
                       }
                    ]
}

Here's some denormalized data:

[
                   {
                     course: "Basic programming",
                     room: "1A",
                     studentId: 1,
                     studentName: "Prashanth"},
                    {
                       course: "Basic programming",
                       room: "1A",
                       studentId: 2,
                       studentName: "Jayaram"
                       }
]

When you parse the JSON container, you will end up in fetching denormalized data in one table.

Let's discuss the different dimensions of the below sample data and represent the data in tabular and JSON file format. Native JSON support in SQL Server 2016 provides you a few functions to read and parse your JSON string into a relational format:

  1. OPENJSON(): Table valued function; parses JSON text and returns row set view of JSON.
  2. JSON_Value(): Scalar function; returns a value from JSON on the specified path.

The below sample output is an example to demonstrate the different dimension of representing the data into a JSON and relational data. The example lists a parent/child realtionship and in a JSON array (batter and topping) and nested objects as well:

ID Type Name Batter Topping
1 donut Cake Regular None
1 donut Cake Regular Glazed
1 donut Cake Regular Sugar
1 donut Cake Regular Powdered Sugar
1 donut Cake Regular Chocolate with Sprinkles
1 donut Cake Regular Chocolate
1 donut Cake Regular Maple
1 donut Cake Chocolate None
1 donut Cake Chocolate Glazed
1 donut Cake Chocolate Sugar
1 donut Cake Chocolate Powdered Sugar
1 donut Cake Chocolate Chocolate with Sprinkles
1 donut Cake Chocolate Chocolate
1 donut Cake Chocolate Maple
1 donut Cake Blueberry None
1 donut Cake Blueberry Glazed
1 donut Cake Blueberry Sugar
1 donut Cake Blueberry Powdered Sugar
1 donut Cake Blueberry Chocolate with Sprinkles
1 donut Cake Blueberry Chocolate
1 donut Cake Blueberry Maple
1 donut Cake Devil's Food None
1 donut Cake Devil's Food Glazed
1 donut Cake Devil's Food Sugar
1 donut Cake Devil's Food Powdered Sugar
1 donut Cake Devil's Food Chocolate with Sprinkles
1 donut Cake Devil's Food Chocolate
1 donut Cake Devil's Food Maple

Transform Above Sample Data to Json

[{
    "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
    "batters":
        {
            "batter":
                [
                    { "id1": "1001", "type1": "Regular" },
                    { "id1": "1002", "type1": "Chocolate" },
                    { "id1": "1003", "type1": "Blueberry" },
                    { "id1": "1004", "type1": "Devils Food" }
                ]
        },
    "topping":
        [
            { "id2": "5001", "type2": "None" },
            { "id2": "5002", "type2": "Glazed" },
            { "id2": "5005", "type2": "Sugar" },
            { "id2": "5007", "type2": "Powdered Sugar" },
            { "id2": "5006", "type2": "Chocolate with Sprinkles" },
            { "id2": "5003", "type2": "Chocolate" },
            { "id2": "5004", "type2": "Maple" }
        ]
},
{
    "id": "0002",
    "type": "donut",
    "name": "cup Cake",
    "ppu": 0.5,
    "batters":
        {
            "batter":
                [
                    { "id1": "1001", "type1": "Regular" },
                    { "id1": "1002", "type1": "Chocolate" },
                    { "id1": "1003", "type1": "Blueberry" },
                    { "id1": "1004", "type1": "Devil's Food" }
                ]
        },
    "topping":
        [
            { "id2": "5001", "type2": "None" },
            { "id2": "5002", "type2": "Glazed" },
            { "id2": "5005", "type2": "Sugar" }

        ]
}
]

JSON2.jpg

Transform JSON to Relational data

OPENJSON is a table-value function (TVF) that looks into JSON text, locates an array of JSON objects, iterates through the elements of the array, and returns one row in the output result for each element.

To read the JSON from the file, load the file using OPENROWSET constructed into a variable. stocks.json is an example for the demonstration. You can derive the path as per your requirements and environment.

The following example shows SQL code that reads the content of the JSON file using the OPENROWSET BULK function and passes the content of JSON file (BulkColumn) to OPENJSON function.

The JSON file can be stored local file system or global (cloud storage).

SELECT ID,type,name,ppu,type1 batter,type2 topping  FROM
OPENROWSET(BULK N'\\hq6021\c$\stocks.json', SINGLE_CLOB) AS json
            CROSS APPLY OPENJSON(BulkColumn)
                        WITH(
                            id nvarchar(40),    type nvarchar(40),
                            name NVARCHAR(MAX),
                            ppu NVARCHAR(MAX) ,
                            batters NVARCHAR(MAX) AS JSON,
                            topping NVARCHAR(MAX) AS JSON ) AS t
CROSS APPLY
OPENJSON(batters,'$.batter')
 WITH (id1 nvarchar(100), type1 nvarchar(20))
CROSS APPLY
OPENJSON(topping)
 WITH (id2 nvarchar(100), type2 nvarchar(20))

json4

Built-In Functions for JSON Processing

SQL Server 2016 provides functions for parsing and processing JSON text. JSON built-in functions that are available in SQL Server 2016 are:

  • ISJSON( jsonText ): Checks if the NVARCHAR text is properly formatted according to the JSON specification. You can use this function to create check constraints on NVARCHAR columns that contain JSON text.
  • JSON_VALUE( jsonText, path ): Parses jsonText and extracts scalar values on the specified JavaScript-like path (see below for some JSON path examples).
  • JSON_QUERY( jsonText, path ): Parses jsonText and extracts objects or arrays on the specified JavaScript-like path (see below for some JSON path examples).

These functions use JSON paths for referencing values or objects in JSON text. JSON paths use JavaScript-like syntax for referencing properties in JSON text. Some examples are:

  • $: References the entire JSON object in the input text.
  • $.property1: References property1 in the JSON object.
  • $[4]: References the 5th element in the JSON array (indexes are counted from 0, like in JavaScript).
  • $.property1.property2.array1[5].property3.array2[15].property4: References the complex nested property in the JSON object.
  • $.info. "first name": References "first name" property in info object. If the key contains some special characters such as space, dollar, etc., it should be surrounded by double quotes.

The dollar sign ($) represents the input JSON object (similar to root / in XPath language). You can add any JavaScript-like property or array after $ to reference properties in JSON object. One simple example of a query where these built-in functions are used is:

DECLARE @MyJSON NVARCHAR(4000) =
N'{
    "info":{ 
      "type":1,
      "address":{ 
        "town":"Louisville",
        "county":"Boulder",
        "country":"USA"
      },
      "tags":["Snow", "Breweries"]
   },
   "LocationType":"East",
   "WeatherType":"Cold"
}'

Select * from  OPENJSON (@MyJSON)
WITH (  
             type   int '$.info.type' , 
             LocationType varchar(20) '$.LocationType', 
             WeatherType varchar(20) '$.WeatherType',
             town varchar(200) '$.info.address.town',
             county varchar(200) '$.info.address.county',
             country varchar(200) '$.info.address.country'

)  AS t
CROSS APPLY
OPENJSON(@MyJSON,'$.info.tags' )

JSON3.jpg

How to Define Nested Objects in JSON

The above examples also contain sample data that represents a nested object.

DECLARE @json NVARCHAR(1000)
SELECT @json =
N'{
  "Employee":
  [
    {
    "Element":1 
    },
    {
    "Element":2
    },
    -
    -
    -
    -
    {
    "Element": "n"
    }

  ]
}'

Parsing Nested Elements of JSON

In the below employee example, employeeDepartment is the root of the JSON. The array element DEPT has a dimension data which represents the department details of each employee. The employee JSON structure has three objects.

DECLARE @MyJSON NVARCHAR(4000) =

    N'{
    "EmployeeDepartment":
    [{      "EmployeeID" : "E0001",
           "FirstName":"Prashanth",
            "LastName":"Jayaram",
             "DOB":"1983-02-03",
             "DEPT":[{"EmployeeDepartment":"Ducks"},{"EmployeeDepartment":"Blues"}]

       },
     {      "EmployeeID" : "E0002",
            "FirstName":"Prarthana",
             "LastName":"Prashanth",
             "DOB":"2015-07-06",
             "DEPT":[{"EmployeeDepartment":"Red Wings"}]
      },
     {      "EmployeeID" : "E0003",
            "FirstName":"Pravitha",
             "LastName":"Prashanth",
             "DOB":"2015-07-06",
             "DEPT":[{"EmployeeDepartment":"Red Wings"},{"EmployeeDepartment":"Green Bird"}]
            }

    ]}'
     -- SELECT * FROM OPENJSON(@MyJSON)
    SELECT
        EmployeeID,
        FirstName,
        LastName,
        DOB,
        DEPT,
        EmployeeDepartment
    FROM OPENJSON (@MyJSON, '$.EmployeeDepartment')
    WITH (
     EmployeeID    varchar(10) ,
     FirstName varchar(25) ,
     LastName varchar(25),
      DOB varchar(25) ,
      DEPT NVARCHAR(MAX) AS JSON
     ) AS E
CROSS APPLY
OPENJSON(DEPT)
 WITH (EmployeeDepartment nvarchar(100))

json1

Reading JSON Into Separate Rows

How about pulling them in separate rows using JSON_Value()with theOPENJSON()function? The below query gives an overview of applying the JSON constructs on the nested elements.

DECLARE @MyJSON NVARCHAR(4000) =
    N'{
    "EmployeeDepartment":
    [{      "EmployeeID" : "E0001",
           "FirstName":"Prashanth",
            "LastName":"Jayaram",
             "DOB":"1983-02-03",
             "DEPT":[{"DeptID":"D1","DName":"Ducks"},{"DeptID":"D2","DName":"Blues"}]

       },
     {      "EmployeeID" : "E0002",
            "FirstName":"Prarthana",
             "LastName":"Prashanth",
             "DOB":"2015-07-06",
             "DEPT":[{"DeptID":"D3","DName":"Red Wings"}]
      },
     {      "EmployeeID" : "E0003",
            "FirstName":"Pravitha",
             "LastName":"Prashanth",
             "DOB":"2015-07-06",
             "DEPT":[{"DeptID":"D3","DName":"Red Wings"},{"DeptID":"D4","DName":"Green Bird"}]
            }

    ]}'
    SELECT
    JSON_Value (c.value, '$.EmployeeID') as EmployeeID,
    JSON_Value (c.value, '$.FirstName') as FirstName,
    JSON_Value (C.value, '$.DOB') as DOB,
    JSON_Value (p.value, '$.DeptID') as DEPTID,
    JSON_Value (p.value, '$.DName') as DName
 FROM OPENJSON (@MyJSON, '$.EmployeeDepartment') as c
CROSS APPLY OPENJSON (c.value, '$.DEPT') as p

json5

Reading JSON Elements Into Separate Columns

You can specify the child elements with the full path by using $ inside the WITH() clause to segregate the data into separate columns.

DECLARE @MyJSON NVARCHAR(4000) =
    N'{
    "EmployeeDepartment":
    [{      "EmployeeID" : "E0001",
           "FirstName":"Prashanth",
            "LastName":"Jayaram",
             "DOB":"1983-02-03",
             "DEPT":[{"DeptID":"D1","DName":"Ducks"},{"DeptID":"D2","DName":"Blues"}]

       },
     {      "EmployeeID" : "E0002",
            "FirstName":"Prarthana",
             "LastName":"Prashanth",
             "DOB":"2015-07-06",
             "DEPT":[{"DeptID":"D3","DName":"Red Wings"}]
      },
     {      "EmployeeID" : "E0003",
            "FirstName":"Pravitha",
             "LastName":"Prashanth",
             "DOB":"2015-07-06",
             "DEPT":[{"DeptID":"D3","DName":"Red Wings"},{"DeptID":"D4","DName":"Green Bird"}]
            }

    ]}'
SELECT
    EmployeeID,
    FirstName,
    DOB,
    Dept1,DName1,
    Dept2,DName2       
FROM OPENJSON (@MyJSON, '$.EmployeeDepartment')
WITH (
    EmployeeID    varchar(20) '$.EmployeeID',
    FirstName varchar(20) '$.FirstName',
    DOB varchar(20) '$.DOB',
    Dept1 varchar(20) '$.DEPT[0].DeptID',
    Dname1 varchar(20) '$.DEPT[0].DName',
    Dept2 varchar(20) '$.DEPT[1].DeptID',
    Dname2 varchar(20) '$.DEPT[1].DName'
) AS EMP

json6

Conclusion

The SQL 2016 contains some very powerful JSON constructs. Mixing the power of relational databases with the flexibility of the JSON offers many benefits from the point of migration, integration, and deployment.

The powerful JSON SQL constructs enable to query and analyze JSON data as well as transform JSON to relational data and relational data to JSON.

There are plenty of examples and resources are available under various links. This is an effort to combine real-world scenarios and details the various ways of JSON data manipulation using SQL 2016 JSON constructs.

NoSQL offers different concepts. Complex structures are placed together into collections of entities where you can take everything you need with one read operation or where you can insert complex structure with a single write. The bad side is that sometimes you want to organize your information in different collections and then you will find that it is very hard to JOIN entities from two collections.

With new SQL server, you have options to choose between these two concepts and use the best of both worlds. In your data models, you can choose when to use traditional relational structures and when to introduce NoSQL concepts.

References

Your API is not enough. Learn why (and how) leading SaaS providers are turning their products into platforms with API integration in the ebook, Build Platforms, Not Products from Cloud Elements.

Topics:
json ,sql ,integration ,data models

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}