We help IT Professionals succeed at work.

JSON Data in SQL Server

Hi, I'm wondoering how to get JSON data from a sql server 2016 column, split out into a table. I've never worked with JSON before, so I'm coming at this like a noob.


Any example would be great.
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
Commented:

I would suggest you start with the JSON functions:

Extract values from JSON text and use them in queries

If you have JSON text that's stored in database tables, you can read or modify values in the JSON text by using the following built-in functions:

There are examples provided:

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15



The links provided by @slightwv above should be a good start.


I just wanted to add that if you have ever worked with XML data in SQL Server, working with JSON data should be very similar for you to understand.

Junior VasquezIT Specialist

Author

Commented:
Hi again,

Having somewhat of an issue, and it's because of my lack of experience with JSON

This is the definition of the JSON File

{"@Primary":"0","Address":"","PSA":"","Type":"Apartment","UpdatedDate":""}

That's not all of the columns, but those values should always be populated:

When I query using this query below, most of the columns are null, which I know is not really the case, because I can see the actual json data in one column:


select JSON_VALUE (FieldValue, '$.Address') as 'Address',JSON_VALUE (FieldValue, '$.Docket') as Docket, JSON_VALUE (FieldValue, '$.Type') as 'Type'
from gss.QueueData
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

Please post sample raw data and expected results so we can set up a test case and provide tested SQL.

Junior VasquezIT Specialist

Author

Commented:
ok, I will try to get that on today
Junior VasquezIT Specialist

Author

Commented:
ok, so this is what the json looks like it's in the column called fieldValue

[  {    "Conviction_Docket": "1995_FEL_xxxxx",    "Conviction_Count": "",    "Conviction_ChargeCode": "011524",    "Conviction_Charge": "*Attempted",    "Conviction_Multiplier": 1,    "Conviction_OffenseType": "Drug",    "Conviction_Severity": "Misdemeanor Not Counted",    "Conviction_Adult": 1,    "Conviction_Juvenile": 0,    "Conviction_Adult_Revived": 0,    "Conviction_Accessary": 0,    "Conviction_Calculation": "0"  },  {    "Conviction_Docket": "2012-CTF-xxxxxx",    "Conviction_Count": "1",    "Conviction_ChargeCode": "000000",    "Conviction_Charge": "Driving Under Influence-1st Off",    "Conviction_Multiplier": 1,    "Conviction_OffenseType": "Other",    "Conviction_Severity": "Misdemeanor",    "Conviction_Adult": 1,    "Conviction_Juvenile": 0,    "Conviction_Adult_Revived": 0,    "Conviction_Accessary": 0,    "Conviction_Calculation": "0.25"  },  {    "Conviction_Docket": "xxxxx-0xx",    "Conviction_Count": "2",    "Conviction_ChargeCode": "0000",    "Conviction_Charge": "DUI (1st) Under Influence 40-716",    "Conviction_Multiplier": 1,    "Conviction_OffenseType": "Other",    "Conviction_Severity": "Duplicate",    "Conviction_Adult": 1,    "Conviction_Juvenile": 0,    "Conviction_Adult_Revived": 0,    "Conviction_Accessary": 0,    "Conviction_Calculation": "0"  },  {    "Conviction_Docket": "000000",    "Conviction_Count": "1",    "Conviction_ChargeCode": "",    "Conviction_Charge": "Possession of CDS",    "Conviction_Multiplier": 1,    "Conviction_OffenseType": "Drug",    "Conviction_Severity": "Misdemeanor",    "Conviction_Adult": 1,    "Conviction_Juvenile": 0,    "Conviction_Adult_Revived": 0,    "Conviction_Accessary": 0,    "Conviction_Calculation": "0.25"  },  {    "Conviction_Docket": "2004 CTF 005113",    "Conviction_Count": "2",    "Conviction_ChargeCode": "",    "Conviction_Charge": "DUI 1st Off",    "Conviction_Multiplier": 1,    "Conviction_OffenseType": "Other",    "Conviction_Severity": "Misdemeanor",    "Conviction_Adult": 1,    "Conviction_Juvenile": 0,    "Conviction_Adult_Revived": 0,    "Conviction_Accessary": 0,    "Conviction_Calculation": "0.25"  }]


I'm looking to pull these parts out of the json file (attached image)
Capture.PNG
Junior VasquezIT Specialist

Author

Commented:
I used the below query, just to see if I could start, but it pulls mostly all null data, but that's not the case....

select JSON_VALUE (FieldValue, '$.Address') as 'Address',JSON_VALUE (FieldValue, '$.Conviction_Docket') as Docket, JSON_VALUE(FieldValue,'$.Conviction_Count') as 'Count',
JSON_VALUE(FieldValue, '$.Conviction_Charge') as 'Charge'
from gss.QueueData
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

I don't see 'Address' in the sample JSON so not sure I have a complete test case.


The '[' at the start designates an array.


Using what you provided, try this:

select Docket, [Count], Charge
from QueueData
   cross apply OPENJSON(fieldvalue, '$' ) 
    WITH (
       Docket VARCHAR(25) '$.Conviction_Docket',
       [Count] VARCHAR(25) '$.Conviction_Count',
       Charge VARCHAR(25) '$.Conviction_Charge'
    );


My complete test can be found here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8e51f5e25d5bbe2a68a97168862df3b6

Junior VasquezIT Specialist

Author

Commented:
This is great!

here is my query finalized: is there a way to filter on the column 'Severity' = 'felony' ???

select Docket, [Count], Charge, ChargeCode, Multiplier, Severity, Adult, Juvenile, Adult_Revived, Accessary, Calculation
from gss.QueueData
   cross apply OPENJSON(fieldvalue, '$' )
    WITH (
       Docket VARCHAR(25) '$.Conviction_Docket',
       [Count] VARCHAR(25) '$.Conviction_Count',
         ChargeCode varchar(25) '$.Conviction_ChargeCode',
       Charge VARCHAR(255) '$.Conviction_Charge',
         [Multiplier] varchar(25) '$.Conviction_Multiplier',
         Severity Varchar(25) '$.Conviction_Severity',
         Adult varchar(25) '$.Conviction_Adult',
         Juvenile varchar(25) '$.Conviction_Juvenile',
         Adult_Revived varchar(25) '$.Conviction_Adult_Revived',
         Accessary varchar(25) '$.Conviction_Accessary',
         Calculation varchar(25) '$.Conviction_Calculation'
    )
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

>> is there a way to filter on the column 'Severity' = 'felony' ???


Add a where clause like any other SQL select.


select Docket, [Count], Charge, ChargeCode, Multiplier, Severity, Adult, Juvenile, Adult_Revived, Accessary, Calculation
from gss.QueueData
   cross apply OPENJSON(fieldvalue, '$' )
    WITH (
       Docket VARCHAR(25) '$.Conviction_Docket',
       [Count] VARCHAR(25) '$.Conviction_Count',
         ChargeCode varchar(25) '$.Conviction_ChargeCode',
       Charge VARCHAR(255) '$.Conviction_Charge',
         [Multiplier] varchar(25) '$.Conviction_Multiplier',
         Severity Varchar(25) '$.Conviction_Severity',
         Adult varchar(25) '$.Conviction_Adult',
         Juvenile varchar(25) '$.Conviction_Juvenile',
         Adult_Revived varchar(25) '$.Conviction_Adult_Revived',
         Accessary varchar(25) '$.Conviction_Accessary',
         Calculation varchar(25) '$.Conviction_Calculation'
    )
 where severity=' felony' ;



The sample you provided didn't have any felonies so here is a test with 'Duplicate':

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=86d580f461cd696576a50e15daa0673c