asked on

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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nakul Vachhrajani

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 Vasquez

ASKER

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

slightwv (䄆 Netminder)

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

Junior Vasquez

ASKER

ok, I will try to get that on today

Junior Vasquez

ASKER

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 Vasquez

ASKER

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

slightwv (䄆 Netminder)

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'
    );

Open in new window

My complete test can be found here:

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

Junior Vasquez

ASKER

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'
)

slightwv (䄆 Netminder)

>> 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