Fun with SQL Server an JSON

SQL Server Oct 14, 2023

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