Post
Ask Question

Basics Of Working With JSON In SQL Server

Introduction

 
This article gives an overview of working with JSON in SQL Server. Learn more about the basic structure of a JSON document with examples.
 

JSON – A Brief Background

 
JSON is an acronym that stands for JavaScript Object Notation, which became popular a little over seventeen years ago. JSON is essentially a data format, popularized by Douglas Crockford, a well-known programmer with an interesting history who was also involved in the development of JavaScript. JSON has nearly replaced XML as a cross-platform data exchange format. It is reported to be lightweight and easier to manipulate compared to XML. In AWS CloudFormation, templates, which are actually JSON (or YAML) formatted documents, are used to describe AWS resources when automating deployments.
 
JSON is also used extensively in NoSQL databases such as the increasingly popular MongoDB. Virtually all the Social Media giants expose APIs that are based on JSON. I am sure you begin to get the idea of how widespread its applications have become. JSON was standardized in 2013 and the latest version of the standard (ECMA-404: The JSON Data Interchange Syntax) was released in 2017.
 
SQL Server introduced support for JSON in SQL Server 2016.
 

JSON Format

 
JSON documents are represented as a series of JSON objects that contain name-value pairs. JSON objects can increase in complexity as we introduce components which are not just single values but arrays in themselves. The following shows the format of a JSON document based on the EMCA-404 standard.
  1. --Listing 1: Sample JSON Document[{  
  2.     "empid": 1,  
  3.     "lastname": "Davis",  
  4.     "firstname": "Sara",  
  5.     "title": "CEO",  
  6.     "titleofcourtesy": "Ms.",  
  7.     "birthdate": "1968-12-08",  
  8.     "hiredate": "2013-05-01",  
  9.     "address": "7890 - 20th Ave. E., Apt. 2A",  
  10.     "city": "Seattle",  
  11.     "region": "WA",  
  12.     "postalcode": "10003",  
  13.     "country": "USA",  
  14.     "phone": "(206) 555-0101"  
  15. }, {  
  16.     "empid": 2,  
  17.     "lastname": "Funk",  
  18.     "firstname": "Don",  
  19.     "title": "Vice President, Sales",  
  20.     "titleofcourtesy": "Dr.",  
  21.     "birthdate": "1972-02-19",  
  22.     "hiredate": "2013-08-14",  
  23.     "address": "9012 W. Capital Way",  
  24.     "city": "Tacoma",  
  25.     "region": "WA",  
  26.     "postalcode": "10001",  
  27.     "country": "USA",  
  28.     "phone": "(206) 555-0100",  
  29.     "mgrid": 1  
  30. }, {  
  31.     "empid": 3,  
  32.     "lastname": "Lew",  
  33.     "firstname": "Judy",  
  34.     "title": "Sales Manager",  
  35.     "titleofcourtesy": "Ms.",  
  36.     "birthdate": "1983-08-30",  
  37.     "hiredate": "2013-04-01",  
  38.     "address": "2345 Moss Bay Blvd.",  
  39.     "city": "Kirkland",  
  40.     "region": "WA",  
  41.     "postalcode": "10007",  
  42.     "country": "USA",  
  43.     "phone": "(206) 555-0103",  
  44.     "mgrid": 2  
  45. }, {  
  46.     "empid": 4,  
  47.     "lastname": "Peled",  
  48.     "firstname": "Yael",  
  49.     "title": "Sales Representative",  
  50.     "titleofcourtesy": "Mrs.",  
  51.     "birthdate": "1957-09-19",  
  52.     "hiredate": "2014-05-03",  
  53.     "address": "5678 Old Redmond Rd.",  
  54.     "city": "Redmond",  
  55.     "region": "WA",  
  56.     "postalcode": "10009",  
  57.     "country": "USA",  
  58.     "phone": "(206) 555-0104",  
  59.     "mgrid": 3  
  60. }, {  
  61.     "empid": 5,  
  62.     "lastname": "Mortensen",  
  63.     "firstname": "Sven",  
  64.     "title": "Sales Manager",  
  65.     "titleofcourtesy": "Mr.",  
  66.     "birthdate": "1975-03-04",  
  67.     "hiredate": "2014-10-17",  
  68.     "address": "8901 Garrett Hill",  
  69.     "city": "London",  
  70.     "postalcode": "10004",  
  71.     "country": "UK",  
  72.     "phone": "(71) 234-5678",  
  73.     "mgrid": 2  
  74. }, {  
  75.     "empid": 6,  
  76.     "lastname": "Suurs",  
  77.     "firstname": "Paul",  
  78.     "title": "Sales Representative",  
  79.     "titleofcourtesy": "Mr.",  
  80.     "birthdate": "1983-07-02",  
  81.     "hiredate": "2014-10-17",  
  82.     "address": "3456 Coventry House, Miner Rd.",  
  83.     "city": "London",  
  84.     "postalcode": "10005",  
  85.     "country": "UK",  
  86.     "phone": "(71) 345-6789",  
  87.     "mgrid": 5  
  88. }, {  
  89.     "empid": 7,  
  90.     "lastname": "King",  
  91.     "firstname": "Russell",  
  92.     "title": "Sales Representative",  
  93.     "titleofcourtesy": "Mr.",  
  94.     "birthdate": "1980-05-29",  
  95.     "hiredate": "2015-01-02",  
  96.     "address": "6789 Edgeham Hollow, Winchester Way",  
  97.     "city": "London",  
  98.     "postalcode": "10002",  
  99.     "country": "UK",  
  100.     "phone": "(71) 123-4567",  
  101.     "mgrid": 5  
  102. }, {  
  103.     "empid": 8,  
  104.     "lastname": "Cameron",  
  105.     "firstname": "Maria",  
  106.     "title": "Sales Representative",  
  107.     "titleofcourtesy": "Ms.",  
  108.     "birthdate": "1978-01-09",  
  109.     "hiredate": "2015-03-05",  
  110.     "address": "4567 - 11th Ave. N.E.",  
  111.     "city": "Seattle",  
  112.     "region": "WA",  
  113.     "postalcode": "10006",  
  114.     "country": "USA",  
  115.     "phone": "(206) 555-0102",  
  116.     "mgrid": 3  
  117. }, {  
  118.     "empid": 9,  
  119.     "lastname": "Doyle",  
  120.     "firstname": "Patricia",  
  121.     "title": "Sales Representative",  
  122.     "titleofcourtesy": "Ms.",  
  123.     "birthdate": "1986-01-27",  
  124.     "hiredate": "2015-11-15",  
  125.     "address": "1234 Houndstooth Rd.",  
  126.     "city": "London",  
  127.     "postalcode": "10008",  
  128.     "country": "UK",  
  129.     "phone": "(71) 456-7890",  
  130.     "mgrid": 5  
  131. }]   
Basic Structure of a JSON Document
Fig. 1 Basic Structure of a JSON Document
 
The document in Listing 1 was extracted from a regular SQL Server database table using the query from Listing 2. Listing 2 shows the feedback from SQL Server Management Studio upon the query execution: “9 Rows affected”. In essence, SQL Server converts each row in the source table to a JSON object. In each object, the column name is translated to the JSON name and the value for that column in that row is represented as the JSON value.
  1. -- Listing 2: Using the FOR JSON Clause    
  2. USE TSQLV4    
  3. GO    
  4. SELECT * FROM HR.Employees     
  5. FOR JSON AUTO;    
  6.     
  7. USE TSQLV4    
  8. GO    
  9. SELECT * FROM HR.Employees     
  10. FOR JSON PATH;     
Returning a ResultSet in JSON Format
Fig. 2 Returning a ResultSet in JSON Format
 

SQL Server JSON Functions

 
In the previous section, we used the FOR JSON clause which is designed to format query results as JSON. SQL Server in its turn provides the following functions to manipulate JSON formats inside SQL Server.
 
 

OPENJSON

 
OPENJSON can be used to revert JSON formatted data to a relational format. Listing 3 shows an example of this using the first object in the sample JSON document referred to in Listing 1. The approach involves first defining a string variable @json and passing our JSON object as a parameter to this variable. We then pass the variable to the OPENJSON function in a SELECT statement. Running the query produces a result set with three columns: key, value, and type. JSON, unlike XML, has type definitions for each value in a document. In this case, we see Type 2 (numeric data) and Type 1 (string data) represented.
  1. --Listing 3 Using OPENJSON  
  2. DECLARE @json NVARCHAR(4000) = N '{    
  3. "empid": 1, "lastname": "Davis", "firstname": "Sara", "title": "CEO", "titleofcourtesy": "Ms.", "birthdate": "1968-12-08", "hiredate": "2013-05-01", "address": "7890 - 20th Ave. E., Apt. 2A", "city": "Seattle", "region": "WA", "postalcode": "10003", "country": "USA", "phone": "(206) 555-0101"  
  4. }  
  5. ';    
  6. SELECT * FROM OPENJSON(@json);   
ResultSet from Listing 3
Fig. 3 ResultSet from Listing 3 
 
In Listing 4, we use the same approach with the entire JSON text including the square brackets [] resulting in the output shown in Fig. 5. Notice the value in the Type column of this output (5) meaning the value we have in the field is a JSON object. Table 1 shows the list of JSON data types.
  1. --Listing 4 Using OPENJSON  
  2. DECLARE @json NVARCHAR(4000) = N '   [{  
  3.     "empid": 1,  
  4.     "lastname": "Davis",  
  5.     "firstname": "Sara",  
  6.     "title": "CEO",  
  7.     "titleofcourtesy": "Ms.",  
  8.     "birthdate": "1968-12-08",  
  9.     "hiredate": "2013-05-01",  
  10.     "address": "7890 - 20th Ave. E., Apt. 2A",  
  11.     "city": "Seattle",  
  12.     "region": "WA",  
  13.     "postalcode": "10003",  
  14.     "country": "USA",  
  15.     "phone": "(206) 555-0101"  
  16. }, {  
  17.     "empid": 2,  
  18.     "lastname": "Funk",  
  19.     "firstname": "Don",  
  20.     "title": "Vice President, Sales",  
  21.     "titleofcourtesy": "Dr.",  
  22.     "birthdate": "1972-02-19",  
  23.     "hiredate": "2013-08-14",  
  24.     "address": "9012 W. Capital Way",  
  25.     "city": "Tacoma",  
  26.     "region": "WA",  
  27.     "postalcode": "10001",  
  28.     "country": "USA",  
  29.     "phone": "(206) 555-0100",  
  30.     "mgrid": 1  
  31. }, {  
  32.     "empid": 3,  
  33.     "lastname": "Lew",  
  34.     "firstname": "Judy",  
  35.     "title": "Sales Manager",  
  36.     "titleofcourtesy": "Ms.",  
  37.     "birthdate": "1983-08-30",  
  38.     "hiredate": "2013-04-01",  
  39.     "address": "2345 Moss Bay Blvd.",  
  40.     "city": "Kirkland",  
  41.     "region": "WA",  
  42.     "postalcode": "10007",  
  43.     "country": "USA",  
  44.     "phone": "(206) 555-0103",  
  45.     "mgrid": 2  
  46. }, {  
  47.     "empid": 4,  
  48.     "lastname": "Peled",  
  49.     "firstname": "Yael",  
  50.     "title": "Sales Representative",  
  51.     "titleofcourtesy": "Mrs.",  
  52.     "birthdate": "1957-09-19",  
  53.     "hiredate": "2014-05-03",  
  54.     "address": "5678 Old Redmond Rd.",  
  55.     "city": "Redmond",  
  56.     "region": "WA",  
  57.     "postalcode": "10009",  
  58.     "country": "USA",  
  59.     "phone": "(206) 555-0104",  
  60.     "mgrid": 3  
  61. }, {  
  62.     "empid": 5,  
  63.     "lastname": "Mortensen",  
  64.     "firstname": "Sven",  
  65.     "title": "Sales Manager",  
  66.     "titleofcourtesy": "Mr.",  
  67.     "birthdate": "1975-03-04",  
  68.     "hiredate": "2014-10-17",  
  69.     "address": "8901 Garrett Hill",  
  70.     "city": "London",  
  71.     "postalcode": "10004",  
  72.     "country": "UK",  
  73.     "phone": "(71) 234-5678",  
  74.     "mgrid": 2  
  75. }, {  
  76.     "empid": 6,  
  77.     "lastname": "Suurs",  
  78.     "firstname": "Paul",  
  79.     "title": "Sales Representative",  
  80.     "titleofcourtesy": "Mr.",  
  81.     "birthdate": "1983-07-02",  
  82.     "hiredate": "2014-10-17",  
  83.     "address": "3456 Coventry House, Miner Rd.",  
  84.     "city": "London",  
  85.     "postalcode": "10005",  
  86.     "country": "UK",  
  87.     "phone": "(71) 345-6789",  
  88.     "mgrid": 5  
  89. }, {  
  90.     "empid": 7,  
  91.     "lastname": "King",  
  92.     "firstname": "Russell",  
  93.     "title": "Sales Representative",  
  94.     "titleofcourtesy": "Mr.",  
  95.     "birthdate": "1980-05-29",  
  96.     "hiredate": "2015-01-02",  
  97.     "address": "6789 Edgeham Hollow, Winchester Way",  
  98.     "city": "London",  
  99.     "postalcode": "10002",  
  100.     "country": "UK",  
  101.     "phone": "(71) 123-4567",  
  102.     "mgrid": 5  
  103. }, {  
  104.     "empid": 8,  
  105.     "lastname": "Cameron",  
  106.     "firstname": "Maria",  
  107.     "title": "Sales Representative",  
  108.     "titleofcourtesy": "Ms.",  
  109.     "birthdate": "1978-01-09",  
  110.     "hiredate": "2015-03-05",  
  111.     "address": "4567 - 11th Ave. N.E.",  
  112.     "city": "Seattle",  
  113.     "region": "WA",  
  114.     "postalcode": "10006",  
  115.     "country": "USA",  
  116.     "phone": "(206) 555-0102",  
  117.     "mgrid": 3  
  118. }, {  
  119.     "empid": 9,  
  120.     "lastname": "Doyle",  
  121.     "firstname": "Patricia",  
  122.     "title": "Sales Representative",  
  123.     "titleofcourtesy": "Ms.",  
  124.     "birthdate": "1986-01-27",  
  125.     "hiredate": "2015-11-15",  
  126.     "address": "1234 Houndstooth Rd.",  
  127.     "city": "London",  
  128.     "postalcode": "10008",  
  129.     "country": "UK",  
  130.     "phone": "(71) 456-7890",  
  131.     "mgrid": 5  
  132. }]  
  133. ';    
  134. SELECT * FROM OPENJSON(@json);  
Fig. 5 ResultSet from Listing 4
 
In order to represent the JSON data as the complete relational table, we started within Listing 2, we must specify the column names and data types we are converting to. We achieve this using the code in Listing 5. By comparing the output we get with the output when we query the HR.Employees table directly, we see that we are getting exactly the same data (See Fig. 6 and 7).
  1. --Listing 5 Using OPENJSON  
  2. DECLARE @json NVARCHAR(4000) = N '   [{  
  3.     "empid": 1,  
  4.     "lastname": "Davis",  
  5.     "firstname": "Sara",  
  6.     "title": "CEO",  
  7.     "titleofcourtesy": "Ms.",  
  8.     "birthdate": "1968-12-08",  
  9.     "hiredate": "2013-05-01",  
  10.     "address": "7890 - 20th Ave. E., Apt. 2A",  
  11.     "city": "Seattle",  
  12.     "region": "WA",  
  13.     "postalcode": "10003",  
  14.     "country": "USA",  
  15.     "phone": "(206) 555-0101"  
  16. }, {  
  17.     "empid": 2,  
  18.     "lastname": "Funk",  
  19.     "firstname": "Don",  
  20.     "title": "Vice President, Sales",  
  21.     "titleofcourtesy": "Dr.",  
  22.     "birthdate": "1972-02-19",  
  23.     "hiredate": "2013-08-14",  
  24.     "address": "9012 W. Capital Way",  
  25.     "city": "Tacoma",  
  26.     "region": "WA",  
  27.     "postalcode": "10001",  
  28.     "country": "USA",  
  29.     "phone": "(206) 555-0100",  
  30.     "mgrid": 1  
  31. }, {  
  32.     "empid": 3,  
  33.     "lastname": "Lew",  
  34.     "firstname": "Judy",  
  35.     "title": "Sales Manager",  
  36.     "titleofcourtesy": "Ms.",  
  37.     "birthdate": "1983-08-30",  
  38.     "hiredate": "2013-04-01",  
  39.     "address": "2345 Moss Bay Blvd.",  
  40.     "city": "Kirkland",  
  41.     "region": "WA",  
  42.     "postalcode": "10007",  
  43.     "country": "USA",  
  44.     "phone": "(206) 555-0103",  
  45.     "mgrid": 2  
  46. }, {  
  47.     "empid": 4,  
  48.     "lastname": "Peled",  
  49.     "firstname": "Yael",  
  50.     "title": "Sales Representative",  
  51.     "titleofcourtesy": "Mrs.",  
  52.     "birthdate": "1957-09-19",  
  53.     "hiredate": "2014-05-03",  
  54.     "address": "5678 Old Redmond Rd.",  
  55.     "city": "Redmond",  
  56.     "region": "WA",  
  57.     "postalcode": "10009",  
  58.     "country": "USA",  
  59.     "phone": "(206) 555-0104",  
  60.     "mgrid": 3  
  61. }, {  
  62.     "empid": 5,  
  63.     "lastname": "Mortensen",  
  64.     "firstname": "Sven",  
  65.     "title": "Sales Manager",  
  66.     "titleofcourtesy": "Mr.",  
  67.     "birthdate": "1975-03-04",  
  68.     "hiredate": "2014-10-17",  
  69.     "address": "8901 Garrett Hill",  
  70.     "city": "London",  
  71.     "postalcode": "10004",  
  72.     "country": "UK",  
  73.     "phone": "(71) 234-5678",  
  74.     "mgrid": 2  
  75. }, {  
  76.     "empid": 6,  
  77.     "lastname": "Suurs",  
  78.     "firstname": "Paul",  
  79.     "title": "Sales Representative",  
  80.     "titleofcourtesy": "Mr.",  
  81.     "birthdate": "1983-07-02",  
  82.     "hiredate": "2014-10-17",  
  83.     "address": "3456 Coventry House, Miner Rd.",  
  84.     "city": "London",  
  85.     "postalcode": "10005",  
  86.     "country": "UK",  
  87.     "phone": "(71) 345-6789",  
  88.     "mgrid": 5  
  89. }, {  
  90.     "empid": 7,  
  91.     "lastname": "King",  
  92.     "firstname": "Russell",  
  93.     "title": "Sales Representative",  
  94.     "titleofcourtesy": "Mr.",  
  95.     "birthdate": "1980-05-29",  
  96.     "hiredate": "2015-01-02",  
  97.     "address": "6789 Edgeham Hollow, Winchester Way",  
  98.     "city": "London",  
  99.     "postalcode": "10002",  
  100.     "country": "UK",  
  101.     "phone": "(71) 123-4567",  
  102.     "mgrid": 5  
  103. }, {  
  104.     "empid": 8,  
  105.     "lastname": "Cameron",  
  106.     "firstname": "Maria",  
  107.     "title": "Sales Representative",  
  108.     "titleofcourtesy": "Ms.",  
  109.     "birthdate": "1978-01-09",  
  110.     "hiredate": "2015-03-05",  
  111.     "address": "4567 - 11th Ave. N.E.",  
  112.     "city": "Seattle",  
  113.     "region": "WA",  
  114.     "postalcode": "10006",  
  115.     "country": "USA",  
  116.     "phone": "(206) 555-0102",  
  117.     "mgrid": 3  
  118. }, {  
  119.     "empid": 9,  
  120.     "lastname": "Doyle",  
  121.     "firstname": "Patricia",  
  122.     "title": "Sales Representative",  
  123.     "titleofcourtesy": "Ms.",  
  124.     "birthdate": "1986-01-27",  
  125.     "hiredate": "2015-11-15",  
  126.     "address": "1234 Houndstooth Rd.",  
  127.     "city": "London",  
  128.     "postalcode": "10008",  
  129.     "country": "UK",  
  130.     "phone": "(71) 456-7890",  
  131.     "mgrid": 5  
  132. }]  
  133. ';    
  134. SELECT * FROM OPENJSON(@json)  
  135. WITH(empid int '$.empid', lastname varchar(100)  
  136.     '$.lastname', firstname varchar(100)  
  137.     '$.firstname', title varchar(100)  
  138.     '$.title', titleofcourtesy varchar(100)  
  139.     '$.titleofcourtesy', birthdate date '$.birthdate', hiredate date '$.hiredate', address varchar(300)  
  140.     '$.address', city varchar(100)  
  141.     '$.city', postalcode int '$.postalcode', country char(2)  
  142.     '$.country', phone varchar(20)  
  143.     '$.phone', mgrid int '$.mgrid');   
Fig. 6 ResultSet from Listing 5
 
Fig. 7 ResultSet from Querying HR.Employees
 

ISJSON

 
The ISJSON function performs a simple test to confirm whether a text document is represented in a valid JSON format. Listing 6 shows two ways of using this function to test a JSON document. By making one small change in the JSON document, we can get SQL Server to return a 0 (meaning: the document is NOT JSON) when we run this query. Just for fun, I will let you figure out the small change I made to the JSON object (see Fig. 8a and 8b).
  1. --Listing 6 Using ISJSON  
  2. --Basic Check  
  3. for JSON Format  
  4. DECLARE @json NVARCHAR(4000) = N '   {  
  5.     "empid": 1,  
  6.     "lastname": "Davis",  
  7.     "firstname": "Sara",  
  8.     "title": "CEO",  
  9.     "titleofcourtesy": "Ms.",  
  10.     "birthdate": "1968-12-08",  
  11.     "hiredate": "2013-05-01",  
  12.     "address": "7890 - 20th Ave. E., Apt. 2A",  
  13.     "city": "Seattle",  
  14.     "region": "WA",  
  15.     "postalcode": "10003",  
  16.     "country": "USA",  
  17.     "phone": "(206) 555-0101"  
  18. }  
  19. ';    
  20. SELECT ISJSON(@json);  
  21. --Check Using WITH Clause and CASE Expression  
  22. DECLARE @json NVARCHAR(4000) = N '   {  
  23.     "empid": 1,  
  24.     "lastname": "Davis",  
  25.     "firstname": "Sara",  
  26.     "title": "CEO",  
  27.     "titleofcourtesy": "Ms.",  
  28.     "birthdate": "1968-12-08",  
  29.     "hiredate": "2013-05-01",  
  30.     "address": "7890 - 20th Ave. E., Apt. 2A",  
  31.     "city": "Seattle",  
  32.     "region": "WA",  
  33.     "postalcode": "10003",  
  34.     "country": "USA",  
  35.     "phone": "(206) 555-0101"  
  36. }  
  37. ';    
  38. WITH JSONTEST as(SELECT ISJSON(@json)[IS JSON ? ])  
  39. SELECT  
  40. CASE[IS JSON ? ]  
  41. WHEN 1 THEN 'YES'  
  42. WHEN 0 THEN 'NO'  
  43. END AS[IS JSON ? ]  
  44. FROM JSONTEST;   
Fig. 8a IS JSON/8b IS NOT JSON
 
It is worth mentioning, that using web sites such as https://jsonformatter.curiousconcept.com you can quickly validate JSON text or format prepared text as JSON.
 

JSON_* Functions

 
In order to demonstrate the use of the functions JSON_VALUE, JSON_QUERY, and JSON_MODIFY, we created a table with a JSON column using the code in Listing 7. Note that the type for the column in question is a regular string data type NVACHAR(MAX). SQL Server does not have a special data type for JSON data in relational tables.
  1. --Listing 7: Creating a Relational Table with JSON Data  
  2. USE[TSQLV4]  
  3. GO  
  4. /****** Object:  Table [HR].[Employees_JSON]    Script Date: 1/13/2020 10:03:52 AM ******/  
  5. SET ANSI_NULLS ON  
  6. GO  
  7. SET QUOTED_IDENTIFIER ON  
  8. GO  
  9. CREATE TABLE[HR]. [Employees_JSON](  
  10.     [empid][int] IDENTITY(1, 1) NOT NULL,  
  11.     [lastname][nvarchar](20) NOT NULL,  
  12.     [firstname][nvarchar](10) NOT NULL,  
  13.     [title][nvarchar](30) NOT NULL,  
  14.     [titleofcourtesy][nvarchar](25) NOT NULL,  
  15.     [birthdate][date] NOT NULL,  
  16.     [hiredate][date] NOT NULL,  
  17.     [address][nvarchar](60) NOT NULL,  
  18.     [city][nvarchar](15) NOT NULL,  
  19.     [region][nvarchar](15) NULL,  
  20.     [postalcode][nvarchar](10) NULL,  
  21.     [country][nvarchar](15) NOT NULL,  
  22.     [phone][nvarchar](24) NOT NULL,  
  23.     [mgrid][int] NULL,  
  24.     [jsondata][nvarchar](max) NULL, CONSTRAINT[PK_Employees_JSON] PRIMARY KEY CLUSTERED(  
  25.         [empid] ASC) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]) ON[PRIMARY]  
  26. GO  
  27. --Insert one row in the HR.Employees_JSON Table  
  28. INSERT INTO[HR]. [Employees_JSON](lastname, firstname, title, titleofcourtesy, birthdate, hiredate, address, city, region, postalcode, country, phone, mgrid, jsondata)  
  29. SELECT TOP 1  
  30. lastname, firstname, title, titleofcourtesy, birthdate, hiredate, address, city, region, postalcode, country, phone, mgrid, N '   {  
  31.     "empid": 1,  
  32.     "lastname": "Davis",  
  33.     "firstname": "Sara",  
  34.     "title": "CEO",  
  35.     "titleofcourtesy": "Ms.",  
  36.     "birthdate": "1968-12-08",  
  37.     "hiredate": "2013-05-01",  
  38.     "address": "7890 - 20th Ave. E., Apt. 2A",  
  39.     "city": "Seattle",  
  40.     "region": "WA",  
  41.     "postalcode": "10003",  
  42.     "country": "USA",  
  43.     "phone": "(206) 555-0101"  
  44. }  
  45. '    
  46. FROM HR.Employees;  
JSON_VALUE and JSON_QUERY appear similar but are different in the sense that while JSON_VALUE extracts scalar values from a JSON text, JSON_QUERY extracts objects or arrays. In other words, you are likely to get an error or a NULL if you try to extract a scalar value from a JSON text using JSON_QUERY. JSON_MODIFY allows you to change a specific value within JSON text that is stored within a column in a relational table. Listing 8 shows simple examples of using the JSON_* functions. While trying this out you will observe that the JSON path name is case sensitive. Microsoft documentation shows more examples of use cases for these functions.
  1. -- Listing 8: JSON_* Samples     
  2. -- Display A Single Columns Using JSON_VALUE    
  3.     
  4. SELECT     
  5. firstname    
  6. ,lastname    
  7. ,JSON_VALUE(jsondata,'$.title') AS Title    
  8. FROM HR.Employees_JSON    
  9.     
  10. -- Display Two Columns Using JSON_VALUE    
  11. SELECT     
  12. firstname    
  13. ,lastname    
  14. ,JSON_VALUE(jsondata,'$.title') AS Title,    
  15. JSON_VALUE(jsondata,'$.titleofcourtesy') AS TitleofCourtesy    
  16. FROM HR.Employees_JSON    
  17.     
  18. -- Attempt QUerying a JSON Value Using JSON_QUERY (NULL Returned)    
  19. SELECT     
  20. firstname    
  21. ,lastname    
  22. ,JSON_VALUE(jsondata,'$.title') AS Title,    
  23. JSON_QUERY(jsondata,'$.titleofcourtesy') AS TitleofCourtesy    
  24. FROM HR.Employees_JSON    
  25.     
  26. -- Query a JSON Object Using JSON_QUERY    
  27. SELECT     
  28. firstname    
  29. ,lastname    
  30. ,JSON_VALUE(jsondata,'$.title') AS Title,    
  31. JSON_QUERY(jsondata,'$') AS TitleofCourtesy    
  32. FROM HR.Employees_JSON    
  33.     
  34. -- Attempt Querying a JSON Object Using JSON_VALUE (NULL Returned)    
  35. SELECT     
  36. firstname    
  37. ,lastname    
  38. ,JSON_VALUE(jsondata,'$') AS Title    
  39. ,JSON_QUERY(jsondata,'$') AS TitleofCourtesy    
  40. FROM HR.Employees_JSON;    
  41.     
  42. -- Update a value in JSON text using JSON_MODIFY    
  43. DECLARE @jsondata varchar(max)    
  44. SELECT @jsondata= jsondata FROM HR.Employees_JSON;    
  45. SET @jsondata = JSON_MODIFY(@jsondata,'$.title','GCEO')    
  46. PRINT @jsondata    
  47.     
  48. UPDATE HR.Employees_JSON     
  49. SET jsondata=@jsondata;    
  50.     
  51. SELECT     
  52. firstname    
  53. ,lastname    
  54. ,JSON_VALUE(jsondata,'$.title') AS Title    
  55. FROM HR.Employees_JSON;     

Conclusion

 
SQL Server provides ample support for JSON thus helping to bridge the gap between SQL and No-SQL world. The functions described in this article as easy to learn and implement. There are more examples of their use as well as additional functions provided in Microsoft documentation. JSON and generally No-SQL is valuable knowledge that will help in the progression of the modern DBAs career.
 
Initially posted here.
 
References
 
More information about JSON can be obtained from the following resources:
  1. http://www.ecma-international.org/publications/files/ECMA-ST/ECMA-404.pdf
  2. https://twobithistory.org/2017/09/21/the-rise-and-rise-of-json.html
  3. https://www.guru99.com/json-vs-xml-difference.html
  4. https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15
  5. https://www.w3schools.com/js/js_json_datatypes.asp
  6. https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15


Similar Articles