Querying SQL Server data stored as JSON in column - performance and tips


Currently I have the following columns

I now need to add upto 60 more analog columns, and have decided to switch to one column to store the analog data as



Open in new window

The data is only ever queried by 1 report, but I need the report to be very fast

Therefore I need help with the following
1. what data type should i store the data as in sql server
2. is sql any good at querying this type of JSON data direct

I'll want to do stuff like
select jsonColumn.an1 
where mydate between 2 dates

Open in new window

i'm using .net framework 4.5 so can do other logic here to pull out data, but was hoping to query it directly if its performant

I'm using sql server 2014 on live, 2016 on dev, and we are planning an upgrade to 2017 on both environments.
Who is Participating?
ste5anSenior DeveloperCommented:
Your 4 columns do not match the desired JSON. They have a different structure.

SQL Server 2016+ has built-in JSON functionality

You can do something like:

        Analog1 INT ,
        Analog2 INT ,
        Analog1Raw INT ,
        Analog2Raw INT

INSERT INTO @Analog ( Analog1 ,
                      Analog2 ,
                      Analog1Raw ,
                      Analog2Raw )
VALUES ( 1, 2, 3, 4 ) ,
       ( 5, 6, 7, 8 );

SELECT A.Analog1 AS [1.an] ,
       A.Analog1Raw AS [1.anr] ,
       A.Analog2 AS [2.an] ,
       A.Analog2Raw AS [2.anr]
FROM   @Analog A

Open in new window

You can query JSON directly, but I'm a more old-school guy. When you know that you need to query it, then store it in a relational manner.

[..] but was hoping to query it directly if its performant [..]

Depends on the use-case. Under normal circumstances is fast enough. But I would say for sure not the fastest. But getting this kind of performance means lots of works, so the question is "Is it that worth?". So you should reconsider or rephrase your performance goals.
Dirk StraussSenior Full Stack DeveloperCommented:
In a .NET application, you can add the Newtonsoft.Json NuGet package and then Query the JSON with LINQ.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.