Fun with SQL Server an JSON
Tags: sqlserver, json
Published: 2023-10-19 16:57:12

---
title: Fun with SQL Server an JSON
published: true
date: 2023-10-14 15:58:42 UTC
tags: SQLServer,JSON
canonical_url: https://blog.bajonczak.com/fin-with-sql-server-an-json/
---

 ![Fun with SQL Server an JSON](https://blog.bajonczak.com/content/images/2023/10/JSON-Data-Type_2021.1_blog.jpg)

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

![Fun with SQL Server an JSON](https://blog.bajonczak.com/content/images/2023/10/image-5.png)

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

![Fun with SQL Server an JSON](https://blog.bajonczak.com/content/images/2023/10/image-6.png)

#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

![Fun with SQL Server an JSON](https://blog.bajonczak.com/content/images/2023/10/image-7.png)

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