Handling JSON with SQL Server

— using https://www.sqlshack.com/the-json_query-function-to-extract-objects-from-json-data/

— ISJSON(): we can check valid JSON using this function

SELECT TOP (1000) [gscData]
,[gscSiteUrl]
,[gscDate]
,[insDate]
,[insDateUTC],
ISJSON(convert(nvarchar, gscData)) as is_json
FROM [dwhlite].[gsc].[dat_google_search_console]
;

— tk: json functions in SQL Server
— ISJSON(): we can check valid JSON using this function
— JSON_VALUE(): It extracts a scalar value from the JSON data
— JSON_MODIFY(): It modifies values in the JSON Data. You should go through Modifying JSON data using JSON_MODIFY() in SQL Server for this function
— JSON_QUERY: It extracts an array or string from JSON in SQL Server

— example from webpage
— tk: result is only first json, because $.employees[0]

DECLARE @data NVARCHAR(4000);
SET @data = N'{
“employees”:
[ {
“name”:”Raj”,
“email”:”raj@gmail.com”,
“age”:32

},
{
“name”:”Mohan”,
“email”:”Mohan@yahoo.com”,
“age”:21

]
}’;
SELECT JSON_QUERY(@data, ‘$.employees[0]’) AS ‘Result’;

— other example
— returns whole json
DECLARE @data NVARCHAR(4000);
SET @data = N'{
“employees”:
[ {
“name”:”Raj”,
“email”:”raj@gmail.com”,
“age”:32

},
{
“name”:”Mohan”,
“email”:”Mohan@yahoo.com”,
“age”:21

]
}’;
SELECT JSON_QUERY(@data) AS ‘Result’;

— using json value function
— As you know, we cannot use the JSON_VALUE() function to retrieve an object or array
— It retrieves a scalar value from a JSON string. You get NULL value in the output if we replace the JSON_MODIFY() function with JSON_VALUE().
— returns NULL
DECLARE @data NVARCHAR(4000);
SET @data = N'{
“employees”:
[ {
“name”:”Raj”,
“email”:”raj@gmail.com”,
“age”:32

},
{
“name”:”Mohan”,
“email”:”Mohan@yahoo.com”,
“age”:21

]
}’;
SELECT JSON_VALUE(@data,’$.employees’) AS ‘Result’;

— Example 2: Retrieve a scalar value using the JSON_QUERY() function
— As you know, we use JSON_VALUE() function to retrieve a scalar value. If we try to retrieve the scalar value using JSON_QUERY() function, let’s see the output.
— returns null
DECLARE @json_data NVARCHAR(4000) =
‘{“Id”:1,”Brand”:”HP”,
“City”:[“Laptop”,”Mobile”]}’
SELECT JSON_QUERY(@json_data,’$.Name’) as Name

— By default, JSON_QUERY() function uses a default path mode lax. In this mode, SQL Server does not raise an error in case of any invalid key or value.
— We might want to raise an error message instead of getting a NULL value in the output. We can use strict mode for getting the error message.

— Example 3: Retrieve JSON by the $ symbol using the JSON_QUERY() function

DECLARE @data NVARCHAR(4000) =
‘{“Employees”:
[{“EmpId”:1,”Name”:”Raj”,
“Address”:{“City”:”Gurgaon”,”Country”:”India”}},
{“EmpId”:2,”Name”:”Sohan”,
“Address”:{“Village”:”Sohna”,”City”:”Jaipur”,”Country”:”India”}}
]
}’
SELECT JSON_QUERY(@data,’$’) Employees_String

— Now, we want to retrieve the Employees array. In this case, we can specify the array that we want to retrieve with the $ symbol.
— In the below, we specify it as $.Employees.
— In the output, we get the employees array without the key. We can note that the array starts and end with a square bracket.
DECLARE @data NVARCHAR(4000) =
‘{“Employees”:
[{“EmpId”:1,”Name”:”Raj”,
“Address”:{“City”:”Gurgaon”,”Country”:”India”}},
{“EmpId”:2,”Name”:”Sohan”,
“Address”:{“Village”:”Sohna”,”City”:”Jaipur”,”Country”:”India”}}
]
}’
SELECT JSON_QUERY(@data,’$.Employees’) Employees_Array

— In the output, we get the employees array without the key. We can note that the array starts and end with a square bracket.

— We can further filter the JSON and get the customer’s address JSON object. Here, we can specify further argument as $.Employees[1].Address.

select @@VERSION;

— json in adventure works
use [AdventureWorks2016_EXT];

select top 10 * FROM Sales.SalesOrder_json;

— tk: also test of isjson functions
SELECT TOP 10
orderitems,
info,
JSON_QUERY(OrderItems, ‘$’) OrderItems,
JSON_QUERY(info, ‘$’) Info,
isjson(orderitems) as is_json_0,
isjson(info) as is_json_1,
isjson(JSON_QUERY(OrderItems, ‘$’)) as is_json_2,
isjson(JSON_QUERY(info, ‘$’)) is_json_3
FROM Sales.SalesOrder_json;

— SQL Functions in combination with JSON_QUERY() function
— We can use SQL functions such as SQL CONCAT in combination with JSON_QUERY functions. You can download WideWorldImporters database.

use [AdventureWorksDW2016_EXT];

SELECT TOP 1 JSON_QUERY(Tags) AS Tags,
JSON_QUERY(CONCAT(‘[“ValidFrom”,”‘, ValidFrom, ‘”,”‘, “ValidTo”, ValidTo, ‘”]’)) ValidityPeriod
FROM Warehouse.StockItems;

— ok, there is no table listed on training website

— tk: test openJSON
— https://learn.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server?view=sql-server-ver16

use [AdventureWorks2016_EXT];

— example from www
DECLARE @json NVARCHAR(MAX)
SET @json='{“name”:”John”,”surname”:”Doe”,”age”:45,”skills”:[“SQL”,”C#”,”MVC”]}’;
SELECT *
FROM OPENJSON(@json);

— example from previous training www with open json (tk)
— TK: OK, OPENJSON WORKS fine, but only when we have data in variable

SELECT
OPENJSON(info) AS DAF
FROM Sales.SalesOrder_json;

— select openjson(‘{}’) — NOK
— but
— select * from openjson(‘{}’) –OK

DECLARE @json NVARCHAR(MAX)
SET @json= (select top 1 info from Sales.SalesOrder_json);

SELECT *
FROM OPENJSON(@json);

— tk – ok, it works

— example from microsoft www:
— DECLARE @json NVARCHAR(MAX)
— Option 2 – OPENJSON output with an explicit structure
DECLARE @json NVARCHAR(MAX)
SET @json =
N'[
{
“Order”: {
“Number”:”SO43659″,
“Date”:”2011-05-31T00:00:00″
},
“AccountNumber”:”AW29825″,
“Item”: {
“Price”:2024.9940,
“Quantity”:1
}
},
{
“Order”: {
“Number”:”SO43661″,
“Date”:”2011-06-01T00:00:00″
},
“AccountNumber”:”AW73565″,
“Item”: {
“Price”:2024.9940,
“Quantity”:3
}
}
]’ ;

SELECT * FROM
OPENJSON ( @json )
WITH (
Number varchar(200) ‘$.Order.Number’ ,
Date datetime ‘$.Order.Date’,
Customer varchar(200) ‘$.AccountNumber’,
Quantity int ‘$.Item.Quantity’
) ;

— example from stackoverflow
— https://stackoverflow.com/questions/54371192/reading-json-array-into-rows-in-sql-server

DECLARE @json nvarchar(max)
SET @json = N'{
“RequestId”: “1”,
“ActionRecs”: [
{“Type”: “Submit”, “Employee”: “Joe”},
{“Type”: “Review”, “Employee”: “Betty”},
{“Type”: “Approve”, “Employee”: “Sam”},
{“Type”: “Approve”, “Employee”: “Bill”}
]
}’

SELECT i.Id, a.[Type], a.[Employee]
FROM OPENJSON(@json) WITH (
Id varchar(5) ‘$.RequestId’,
ActionRecs nvarchar(max) ‘$.ActionRecs’ AS JSON
) AS i
CROSS APPLY OPENJSON(i.ActionRecs) WITH (
[Type] nvarchar(max) ‘$.Type’,
[Employee] nvarchar(max) ‘$.Employee’
) a
;

— tk examples
— JSON VALUE to get json object from another json object
select top 1
info,
ISJSON(info) as f1,
JSON_QUERY(info, ‘$’) as f2,
JSON_QUERY(info, ‘$.ShippingInfo’) as f3
from
Sales.SalesOrder_json
;

select top 1
info,
ISJSON(OrderItems) as f1,
JSON_QUERY(OrderItems, ‘$’) as f2,
JSON_QUERY(OrderItems, ‘$.ShippingInfo’) as f3, — returns null because not exist
JSON_QUERY(OrderItems, ‘$[0].Item’) as f4,
JSON_VALUE(OrderItems, ‘$[0].Item.Qty’) as f5,
JSON_QUERY(OrderItems, ‘$[0].Product’) as f6,
JSON_VALUE(OrderItems, ‘$[0].Product.Name’) as f7
from
Sales.SalesOrder_json
;

— TK json object into rows: WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SELECT
a.[SalesOrderID],
a.[RevisionNumber],
z.value as Item
FROM Sales.SalesOrder_json a
CROSS APPLY OPENJSON(OrderItems,’$[0].Item’) z
;

— TK: JSON LIST INTO rows
— use
select * from
[Person].[Person_json]
;

— tk – with arays -explode WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
with aaa as (
select
1 as id,
N'[1, 2, 3, 4]’ as json_in
)
select
id,
json_in,
isjson(json_in) as is_json,
z.value as value_signle
from aaa
cross apply openjson(json_in, ‘$’) z
;

Leave a comment Cancel reply