JSON Data in SQL Server

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.

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

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

ok, I will try to get that on today

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

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

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

Select allOpen in new window

My complete test can be found here:

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

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

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