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.
websssCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.