Transform JSON data into rowset

JSON is a popular data format used for storing unstructured data. Many RESTful web services return results and accept inputs in JSON format. Starting from SQL Server 2016, it has native support on parsing JSON data. This blog post demonstrates how to transform JSON data into a rows and columns, and joining the result with table data.

Firstly, let us create a table named Products, which stores the product details into a text column with JSON format:

CREATE TABLE Products (
    id int NOT NULL PRIMARY KEY,
    [name] varchar(50) NOT NULL,
    detailsInJson nvarchar(4000) NOT NULL
);

Then populate some sample rows into it:

INSERT Products (id, [name], detailsInJson) VALUES (1, 'Toy Car',     '{"size" : "10cm x 20cm x 30cm", "color" : "red", "type" : "mini"}');
INSERT Products (id, [name], detailsInJson) VALUES (2, 'Teddy Bear', '{"color" : "brown", "texture" : "cloth"}');

Let's double check the table data:

As you can see, each row has its own set of attributes in the JSON data.
We can retrieve the JSON data using OPENJSON function as below:

SELECT id, [name], D.*
FROM Products
CROSS APPLY OPENJSON (detailsInJson) WITH (
    color varchar(50) '$.color',
    size varchar(50) '$.size',
    [type] varchar(50) '$.type',
    [texture] varchar(50) '$.texture'
) AS D;

Below is the query result:

You can also check the execution plan of this query, OPENJSON is essentially a Table Valued Function which do the transformation:

SQL Server 2016 introduces various new built-in functions to process JSON data, which can be found in Microsoft SQL Docs.