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

Hi

Currently I have the following columns
Analog1
Analog2
Analog1Raw
Analog2Raw

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

[    
    {
        "an1":2103,
        "an1r":2220
    },

    {
        "an2":5999,
        "an2r":6000
    },
etc..
]

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.
websssAsked:
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:

DECLARE @Analog TABLE
    (
        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
FOR JSON PATH;

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.
1
 
Dirk StraussSenior Full Stack DeveloperCommented:
In a .NET application, you can add the Newtonsoft.Json NuGet package and then Query the JSON with LINQ.
0
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.