Yes everyone know SQL and can write anything with it. But what about JSON values? Are you trying to parse and extract JSONS with complex string replacements or s.th. else?
Let me introduce what SQL Server can do for you in this case
Lets create the baseline
For having fun with json let's create the baseline first
CREATE TABLE FunnyData
(
MyJsonData varchar(max)
)
For our examples let's insert the following Json into the Database
{
"LastName":"Doe",
"FirstName":"John",
"Jobtitle":"Developer",
"Age":17,
"Income":500.00
},
{
"LastName":"Fem",
"FirstName":"Josi",
"Jobtitle":"Senior Developer",
"Gender":"Female",
"Age":17,
"Income":1500.00
},
{
"LastName":"PAtrick",
"FirstName":"Jorst",
"Jobtitle":"Architect",
"Age":17,
"Income":2500.00
},
To insert it into the Database we will use this Sql Command
insert into FunnyData (MyJsonData) values ('{
"LastName":"Doe",
"FirstName":"John",
"Jobtitle":"Developer",
"Age":17,
"Income":500.00
}')
insert into FunnyData (MyJsonData) values ('{
"LastName":"Fem",
"FirstName":"Josi",
"Jobtitle":"Senior Developer",
"Gender":"Female",
"Age":17,
"Income":1500.00
}')
insert into FunnyData (MyJsonData) values ('{
"LastName":"PAtrick",
"FirstName":"Jorst",
"Jobtitle":"Architect",
"Age":17,
"Income":2500.00
}')
Using JSON for filtering the data
Lets assume you want to get a t0tal value of all income values. For this you can do the following query
select
sum(
cast(
JSON_VALUE(MYJSONDATA,'$.Income')
as decimal(10,2))
)
from funnydata
This is a very basic command that will select all Json Properties with Path "$.Income" and generate a total value over all items. But be careful, every return value will be a varchar value. So you must convert it to the target datatype to use the sum comment.
Display Data in Selection as Columns
Sometimes you must get all Json Data selected as columns. Let's assume you must generate a table that will result the Income and the FirstName of the Entries. Then the comment will look like this:
SELECT Income,FirstName
FROM FunnyData
CROSS APPLY OPENJSON(MYJSONDATA)
WITH (
Income decimal(10,2) '$.Income',
FirstName varchar(50) '$.FirstName'
) AS JsonData
This will to a cross apply of an json value. The Crossapply will require a column definition for each selected property. So Income will map to the Json Path $.Income and transform the datatype to a decimal value.
The result looks then like this
Of course you can apply filtering and sorting on the "created column" like the normal statement. Let's assume you and all data with an income above 500. Then the query looks like this
SELECT Income,FirstName
FROM FunnyData
CROSS APPLY OPENJSON(MYJSONDATA)
WITH (
Income decimal(10,2) '$.Income',
FirstName varchar(50) '$.FirstName'
) AS JsonData
where Income>500
The result then is this
Create a column based on a JSON Property
Let's assume you can be sure that you have in every JSON the field LastName, then it is possible to create a new column that points to this particular field. In our Example, you can do this with this command
ALTER TABLE FunnyData ADD vLastName AS JSON_VALUE(MYJSONDATA,'$.LastName')
This will map the new column "vLastName" to the Property LastName in the Json Field. So after applying the Alter Table command, you can query the data as normal
Select * from FunnyData
Then you will get the Property as a column, without a complex cross-apply
The advantage against the cross-apply to this solution is, that you now can create an index onto this column and you will have fast selects.
Final Thoughts
This article give you a brief introduction how SQL Server can handle JSON Data and how you can effectively use it.
Leave me comment about your thoughts
Top comments (0)