{{announcement.body}}
{{announcement.title}}

Basics of Working with JSON in SQL Server

DZone 's Guide to

Basics of Working with JSON in SQL Server

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

· Database Zone ·
Free Resource

JSON – A Brief Background

JSON is an acronym for JavaScript Object Notation, that became popular a little over seventeen years ago. JSON is essentially a data format, it was 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:

JSON
 




x
142


 
1
-- Listing 1: Sample JSON Document
2
[
3
{
4
"empid":1,
5
"lastname":"Davis",
6
"firstname":"Sara",
7
"title":"CEO",
8
"titleofcourtesy":"Ms.",
9
"birthdate":"1968-12-08",
10
"hiredate":"2013-05-01",
11
"address":"7890 - 20th Ave. E., Apt. 2A",
12
"city":"Seattle",
13
"region":"WA",
14
"postalcode":"10003",
15
"country":"USA",
16
"phone":"(206) 555-0101"
17
},
18
{
19
"empid":2,
20
"lastname":"Funk",
21
"firstname":"Don",
22
"title":"Vice President, Sales",
23
"titleofcourtesy":"Dr.",
24
"birthdate":"1972-02-19",
25
"hiredate":"2013-08-14",
26
"address":"9012 W. Capital Way",
27
"city":"Tacoma",
28
"region":"WA",
29
"postalcode":"10001",
30
"country":"USA",
31
"phone":"(206) 555-0100",
32
"mgrid":1
33
},
34
{
35
"empid":3,
36
"lastname":"Lew",
37
"firstname":"Judy",
38
"title":"Sales Manager",
39
"titleofcourtesy":"Ms.",
40
"birthdate":"1983-08-30",
41
"hiredate":"2013-04-01",
42
"address":"2345 Moss Bay Blvd.",
43
"city":"Kirkland",
44
"region":"WA",
45
"postalcode":"10007",
46
"country":"USA",
47
"phone":"(206) 555-0103",
48
"mgrid":2
49
},
50
{
51
"empid":4,
52
"lastname":"Peled",
53
"firstname":"Yael",
54
"title":"Sales Representative",
55
"titleofcourtesy":"Mrs.",
56
"birthdate":"1957-09-19",
57
"hiredate":"2014-05-03",
58
"address":"5678 Old Redmond Rd.",
59
"city":"Redmond",
60
"region":"WA",
61
"postalcode":"10009",
62
"country":"USA",
63
"phone":"(206) 555-0104",
64
"mgrid":3
65
},
66
{
67
"empid":5,
68
"lastname":"Mortensen",
69
"firstname":"Sven",
70
"title":"Sales Manager",
71
"titleofcourtesy":"Mr.",
72
"birthdate":"1975-03-04",
73
"hiredate":"2014-10-17",
74
"address":"8901 Garrett Hill",
75
"city":"London",
76
"postalcode":"10004",
77
"country":"UK",
78
"phone":"(71) 234-5678",
79
"mgrid":2
80
},
81
{
82
"empid":6,
83
"lastname":"Suurs",
84
"firstname":"Paul",
85
"title":"Sales Representative",
86
"titleofcourtesy":"Mr.",
87
"birthdate":"1983-07-02",
88
"hiredate":"2014-10-17",
89
"address":"3456 Coventry House, Miner Rd.",
90
"city":"London",
91
"postalcode":"10005",
92
"country":"UK",
93
"phone":"(71) 345-6789",
94
"mgrid":5
95
},
96
{
97
"empid":7,
98
"lastname":"King",
99
"firstname":"Russell",
100
"title":"Sales Representative",
101
"titleofcourtesy":"Mr.",
102
"birthdate":"1980-05-29",
103
"hiredate":"2015-01-02",
104
"address":"6789 Edgeham Hollow, Winchester Way",
105
"city":"London",
106
"postalcode":"10002",
107
"country":"UK",
108
"phone":"(71) 123-4567",
109
"mgrid":5
110
},
111
{
112
"empid":8,
113
"lastname":"Cameron",
114
"firstname":"Maria",
115
"title":"Sales Representative",
116
"titleofcourtesy":"Ms.",
117
"birthdate":"1978-01-09",
118
"hiredate":"2015-03-05",
119
"address":"4567 - 11th Ave. N.E.",
120
"city":"Seattle",
121
"region":"WA",
122
"postalcode":"10006",
123
"country":"USA",
124
"phone":"(206) 555-0102",
125
"mgrid":3
126
},
127
{
128
"empid":9,
129
"lastname":"Doyle",
130
"firstname":"Patricia",
131
"title":"Sales Representative",
132
"titleofcourtesy":"Ms.",
133
"birthdate":"1986-01-27",
134
"hiredate":"2015-11-15",
135
"address":"1234 Houndstooth Rd.",
136
"city":"London",
137
"postalcode":"10008",
138
"country":"UK",
139
"phone":"(71) 456-7890",
140
"mgrid":5
141
}
142
]


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. 

Returning a ResultSet in JSON Format

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.

JSON
 




x
16


 
1
-- Listing 3 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", "region":"WA", 
12
"postalcode":"10003", 
13
"country":"USA", 
14
"phone":"(206) 555-0101" }';
15
 
          
16
SELECT * FROM OPENJSON (@json);


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.

JSON Data types

JSON
 




xxxxxxxxxx
1
143


1
-- Listing 4 Using OPENJSON
2
DECLARE @json NVARCHAR(4000) = N'
3
[{
4
"empid":1,
5
"lastname":"Davis",
6
"firstname":"Sara",
7
"title":"CEO",
8
"titleofcourtesy":"Ms.",
9
"birthdate":"1968-12-08",
10
"hiredate":"2013-05-01",
11
"address":"7890 - 20th Ave. E., Apt. 2A",
12
"city":"Seattle",
13
"region":"WA",
14
"postalcode":"10003",
15
"country":"USA",
16
"phone":"(206) 555-0101"
17
},
18
{
19
"empid":2,
20
"lastname":"Funk",
21
"firstname":"Don",
22
"title":"Vice President, Sales",
23
"titleofcourtesy":"Dr.",
24
"birthdate":"1972-02-19",
25
"hiredate":"2013-08-14",
26
"address":"9012 W. Capital Way",
27
"city":"Tacoma",
28
"region":"WA",
29
"postalcode":"10001",
30
"country":"USA",
31
"phone":"(206) 555-0100",
32
"mgrid":1
33
},
34
{
35
"empid":3,
36
"lastname":"Lew",
37
"firstname":"Judy",
38
"title":"Sales Manager",
39
"titleofcourtesy":"Ms.",
40
"birthdate":"1983-08-30",
41
"hiredate":"2013-04-01",
42
"address":"2345 Moss Bay Blvd.",
43
"city":"Kirkland",
44
"region":"WA",
45
"postalcode":"10007",
46
"country":"USA",
47
"phone":"(206) 555-0103",
48
"mgrid":2
49
},
50
{
51
"empid":4,
52
"lastname":"Peled",
53
"firstname":"Yael",
54
"title":"Sales Representative",
55
"titleofcourtesy":"Mrs.",
56
"birthdate":"1957-09-19",
57
"hiredate":"2014-05-03",
58
"address":"5678 Old Redmond Rd.",
59
"city":"Redmond",
60
"region":"WA",
61
"postalcode":"10009",
62
"country":"USA",
63
"phone":"(206) 555-0104",
64
"mgrid":3
65
},
66
{
67
"empid":5,
68
"lastname":"Mortensen",
69
"firstname":"Sven",
70
"title":"Sales Manager",
71
"titleofcourtesy":"Mr.",
72
"birthdate":"1975-03-04",
73
"hiredate":"2014-10-17",
74
"address":"8901 Garrett Hill",
75
"city":"London",
76
"postalcode":"10004",
77
"country":"UK",
78
"phone":"(71) 234-5678",
79
"mgrid":2
80
},
81
{
82
"empid":6,
83
"lastname":"Suurs",
84
"firstname":"Paul",
85
"title":"Sales Representative",
86
"titleofcourtesy":"Mr.",
87
"birthdate":"1983-07-02",
88
"hiredate":"2014-10-17",
89
"address":"3456 Coventry House, Miner Rd.",
90
"city":"London",
91
"postalcode":"10005",
92
"country":"UK",
93
"phone":"(71) 345-6789",
94
"mgrid":5
95
},
96
{
97
"empid":7,
98
"lastname":"King",
99
"firstname":"Russell",
100
"title":"Sales Representative",
101
"titleofcourtesy":"Mr.",
102
"birthdate":"1980-05-29",
103
"hiredate":"2015-01-02",
104
"address":"6789 Edgeham Hollow, Winchester Way",
105
"city":"London",
106
"postalcode":"10002",
107
"country":"UK",
108
"phone":"(71) 123-4567",
109
"mgrid":5
110
},
111
{
112
"empid":8,
113
"lastname":"Cameron",
114
"firstname":"Maria",
115
"title":"Sales Representative",
116
"titleofcourtesy":"Ms.",
117
"birthdate":"1978-01-09",
118
"hiredate":"2015-03-05",
119
"address":"4567 - 11th Ave. N.E.",
120
"city":"Seattle",
121
"region":"WA",
122
"postalcode":"10006",
123
"country":"USA",
124
"phone":"(206) 555-0102",
125
"mgrid":3
126
},
127
{
128
"empid":9,
129
"lastname":"Doyle",
130
"firstname":"Patricia",
131
"title":"Sales Representative",
132
"titleofcourtesy":"Ms.",
133
"birthdate":"1986-01-27",
134
"hiredate":"2015-11-15",
135
"address":"1234 Houndstooth Rd.",
136
"city":"London",
137
"postalcode":"10008",
138
"country":"UK",
139
"phone":"(71) 456-7890",
140
"mgrid":5
141
}]';
142
 
          
143
SELECT * FROM OPENJSON (@json);


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

JSON
 




xxxxxxxxxx
1
159


 
1
-- Listing 5 Using OPENJSON
2
DECLARE @json NVARCHAR(4000) = N'
3
[{
4
"empid":1,
5
"lastname":"Davis",
6
"firstname":"Sara",
7
"title":"CEO",
8
"titleofcourtesy":"Ms.",
9
"birthdate":"1968-12-08",
10
"hiredate":"2013-05-01",
11
"address":"7890 - 20th Ave. E., Apt. 2A",
12
"city":"Seattle",
13
"region":"WA",
14
"postalcode":"10003",
15
"country":"USA",
16
"phone":"(206) 555-0101"
17
},
18
{
19
"empid":2,
20
"lastname":"Funk",
21
"firstname":"Don",
22
"title":"Vice President, Sales",
23
"titleofcourtesy":"Dr.",
24
"birthdate":"1972-02-19",
25
"hiredate":"2013-08-14",
26
"address":"9012 W. Capital Way",
27
"city":"Tacoma",
28
"region":"WA",
29
"postalcode":"10001",
30
"country":"USA",
31
"phone":"(206) 555-0100",
32
"mgrid":1
33
},
34
{
35
"empid":3,
36
"lastname":"Lew",
37
"firstname":"Judy",
38
"title":"Sales Manager",
39
"titleofcourtesy":"Ms.",
40
"birthdate":"1983-08-30",
41
"hiredate":"2013-04-01",
42
"address":"2345 Moss Bay Blvd.",
43
"city":"Kirkland",
44
"region":"WA",
45
"postalcode":"10007",
46
"country":"USA",
47
"phone":"(206) 555-0103",
48
"mgrid":2
49
},
50
{
51
"empid":4,
52
"lastname":"Peled",
53
"firstname":"Yael",
54
"title":"Sales Representative",
55
"titleofcourtesy":"Mrs.",
56
"birthdate":"1957-09-19",
57
"hiredate":"2014-05-03",
58
"address":"5678 Old Redmond Rd.",
59
"city":"Redmond",
60
"region":"WA",
61
"postalcode":"10009",
62
"country":"USA",
63
"phone":"(206) 555-0104",
64
"mgrid":3
65
},
66
{
67
"empid":5,
68
"lastname":"Mortensen",
69
"firstname":"Sven",
70
"title":"Sales Manager",
71
"titleofcourtesy":"Mr.",
72
"birthdate":"1975-03-04",
73
"hiredate":"2014-10-17",
74
"address":"8901 Garrett Hill",
75
"city":"London",
76
"postalcode":"10004",
77
"country":"UK",
78
"phone":"(71) 234-5678",
79
"mgrid":2
80
},
81
{
82
"empid":6,
83
"lastname":"Suurs",
84
"firstname":"Paul",
85
"title":"Sales Representative",
86
"titleofcourtesy":"Mr.",
87
"birthdate":"1983-07-02",
88
"hiredate":"2014-10-17",
89
"address":"3456 Coventry House, Miner Rd.",
90
"city":"London",
91
"postalcode":"10005",
92
"country":"UK",
93
"phone":"(71) 345-6789",
94
"mgrid":5
95
},
96
{
97
"empid":7,
98
"lastname":"King",
99
"firstname":"Russell",
100
"title":"Sales Representative",
101
"titleofcourtesy":"Mr.",
102
"birthdate":"1980-05-29",
103
"hiredate":"2015-01-02",
104
"address":"6789 Edgeham Hollow, Winchester Way",
105
"city":"London",
106
"postalcode":"10002",
107
"country":"UK",
108
"phone":"(71) 123-4567",
109
"mgrid":5
110
},
111
{
112
"empid":8,
113
"lastname":"Cameron",
114
"firstname":"Maria",
115
"title":"Sales Representative",
116
"titleofcourtesy":"Ms.",
117
"birthdate":"1978-01-09",
118
"hiredate":"2015-03-05",
119
"address":"4567 - 11th Ave. N.E.",
120
"city":"Seattle",
121
"region":"WA",
122
"postalcode":"10006",
123
"country":"USA",
124
"phone":"(206) 555-0102",
125
"mgrid":3
126
},
127
{
128
"empid":9,
129
"lastname":"Doyle",
130
"firstname":"Patricia",
131
"title":"Sales Representative",
132
"titleofcourtesy":"Ms.",
133
"birthdate":"1986-01-27",
134
"hiredate":"2015-11-15",
135
"address":"1234 Houndstooth Rd.",
136
"city":"London",
137
"postalcode":"10008",
138
"country":"UK",
139
"phone":"(71) 456-7890",
140
"mgrid":5
141
}]';
142
 
          
143
SELECT * FROM OPENJSON (@json) 
144
WITH (
145
 
          
146
empid int '$.empid',
147
lastname varchar(100) '$.lastname',
148
firstname varchar(100) '$.firstname',
149
title varchar(100) '$.title',
150
titleofcourtesy varchar(100) '$.titleofcourtesy',
151
birthdate date '$.birthdate',
152
hiredate date '$.hiredate',
153
address varchar(300) '$.address',
154
city varchar(100) '$.city',
155
postalcode int '$.postalcode',
156
country char(2) '$.country',
157
phone varchar(20) '$.phone',
158
mgrid int '$.mgrid')
159
;


ResultSet from Listing 5

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

JSON
 




xxxxxxxxxx
1
49


1
-- Listing 6 Using ISJSON
2
-- Basic Check for JSON Format
3
 
          
4
DECLARE @json NVARCHAR(4000) = N'
5
{
6
"empid":1,
7
"lastname":"Davis",
8
"firstname":"Sara",
9
"title":"CEO",
10
"titleofcourtesy":"Ms.",
11
"birthdate":"1968-12-08",
12
"hiredate":"2013-05-01",
13
"address":"7890 - 20th Ave. E., Apt. 2A",
14
"city":"Seattle",
15
"region":"WA",
16
"postalcode":"10003",
17
"country":"USA",
18
"phone":"(206) 555-0101"
19
}';
20
 
          
21
SELECT ISJSON (@json);
22
 
          
23
 
          
24
-- Check Using WITH Clause and CASE Expression
25
 
          
26
DECLARE @json NVARCHAR(4000) = N'
27
{
28
"empid":1,
29
"lastname":"Davis",
30
"firstname":"Sara",
31
"title":"CEO",
32
"titleofcourtesy":"Ms.",
33
"birthdate":"1968-12-08",
34
"hiredate":"2013-05-01",
35
"address":"7890 - 20th Ave. E., Apt. 2A",
36
"city":"Seattle",
37
"region":"WA",
38
"postalcode":"10003",
39
"country":"USA",
40
"phone":"(206) 555-0101"
41
}';
42
 
          
43
WITH JSONTEST as (SELECT ISJSON (@json) [IS JSON ?] )
44
SELECT 
45
CASE [IS JSON ?] 
46
WHEN 1 THEN 'YES'
47
WHEN 0 THEN 'NO'
48
END AS [IS JSON ?]
49
FROM JSONTEST;


IS vs IS NOT

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 create 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.

JSON
 




xxxxxxxxxx
1
84


 
1
-- Listing 7: Creating a Relational Table with JSON Data
2
 
          
3
USE [TSQLV4]
4
GO
5
 
          
6
/****** Object:  Table [HR].[Employees_JSON]    Script Date: 1/13/2020 10:03:52 AM ******/
7
SET ANSI_NULLS ON
8
GO
9
 
          
10
SET QUOTED_IDENTIFIER ON
11
GO
12
 
          
13
CREATE TABLE [HR].[Employees_JSON](
14
    [empid] [int] IDENTITY(1,1) NOT NULL,
15
    [lastname] [nvarchar](20) NOT NULL,
16
    [firstname] [nvarchar](10) NOT NULL,
17
    [title] [nvarchar](30) NOT NULL,
18
    [titleofcourtesy] [nvarchar](25) NOT NULL,
19
    [birthdate] [date] NOT NULL,
20
    [hiredate] [date] NOT NULL,
21
    [address] [nvarchar](60) NOT NULL,
22
    [city] [nvarchar](15) NOT NULL,
23
    [region] [nvarchar](15) NULL,
24
    [postalcode] [nvarchar](10) NULL,
25
    [country] [nvarchar](15) NOT NULL,
26
    [phone] [nvarchar](24) NOT NULL,
27
    [mgrid] [int] NULL,
28
    [jsondata] [nvarchar](max) NULL,
29
 CONSTRAINT [PK_Employees_JSON] PRIMARY KEY CLUSTERED 
30
(
31
    [empid] ASC
32
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
33
) ON [PRIMARY]
34
GO
35
 
          
36
-- Insert one row in the HR.Employees_JSON Table
37
 
          
38
INSERT INTO [HR].[Employees_JSON] (
39
lastname
40
, firstname
41
, title
42
, titleofcourtesy
43
, birthdate
44
, hiredate
45
, address
46
, city
47
, region
48
, postalcode
49
, country
50
, phone
51
, mgrid
52
, jsondata)
53
 
          
54
SELECT TOP 1 
55
lastname
56
, firstname
57
, title
58
, titleofcourtesy
59
, birthdate
60
, hiredate
61
, address
62
, city
63
, region
64
, postalcode
65
, country
66
, phone
67
, mgrid
68
,N'
69
{
70
"empid":1,
71
"lastname":"Davis",
72
"firstname":"Sara",
73
"title":"CEO",
74
"titleofcourtesy":"Ms.",
75
"birthdate":"1968-12-08",
76
"hiredate":"2013-05-01",
77
"address":"7890 - 20th Ave. E., Apt. 2A",
78
"city":"Seattle",
79
"region":"WA",
80
"postalcode":"10003",
81
"country":"USA",
82
"phone":"(206) 555-0101"
83
}'
84
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. 

JSON
 




xxxxxxxxxx
1
55


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.

Topics:
json, sql, sql server

Published at DZone with permission of Nathan Russel . See the original article here.

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}