Parsing JSON from SQL Server

Question

I have a table with the following columns, one column being a JSON blob. I'm unclear how to parse the JSON blob as a series of columns alongside the other columns. I know there's something called OPENJSON, but not sure how to apply it to this case.

ID  |   ORGANIZATION   |  DEVICE_TIME  |   DEVICE   |     DATA 
--------------------------------------------------------------------  
011        015             2021-07-20       015        (JSON COLUMN)
012        016             2021-08-20       016        (JSON COLUMN)

The json string example is below, from the DATA column above

{
    "device":   {
        "battery_level":    98,
        "rssi": -105,
        "boot_cnt": 5,
        "apn":  "teal",
        "ip_addr":  "10.176.30.171",
        "fw_ver":   "1.00",
        "modem_fw": "mfw_nrf9160_1.3.0",
        "imsi": "234500024531391",
        "imei": "352656101040510",
        "iccid":    "8901990000000534985"
    },
     "data":    {
       "Temperature":   77.563942718505871,
       "Humidity":  29.100597381591797,
       "pressure":  28.883883226248145,
       "air_quality":   37.067466735839844,
       "SoilMoisture":  0.42462845010615713,
       "Lat":   0,
       "Long":  0,
       "Alt":   0
    }
}

OPENJSON returns a table (with possible multiple rows). You want a scalar. You want JSON_VALUE. I will post an answer shortly. — Nick.Mc, Jul 21 '21 at 05:42
As per the question guide, please show us what you have tried and where you got stuck. — Dale K, Jul 21 '21 at 06:07
Thank you Dale, looks like my question got downvoted due to not properly stating that fact which is unfortunate. The question was solved further down, so this will hopefully not be downvoted any further. — RalphBiggerton, Jul 21 '21 at 16:19

Nick.Mc · Accepted Answer · 2021-07-21T05:55:33.853

openjson returns a table (possibly with many rows, although not for your sample).

To put something into a column you need a scalar. Try this example. Yes you need to explicitly list the columns out.

/* Create a sample table */
WITH MySampleTable
AS (
SELECT 1 as col1, 2 as col2, 'Hi There' as col3,
CAST('
{
    "device":   {
        "battery_level":    98,
        "rssi": -105,
        "boot_cnt": 5,
        "apn":  "teal",
        "ip_addr":  "10.176.30.171",
        "fw_ver":   "1.00",
        "modem_fw": "mfw_nrf9160_1.3.0",
        "imsi": "234500024531391",
        "imei": "352656101040510",
        "iccid":    "8901990000000534985"
    },
     "data":    {
       "Temperature":   77.563942718505871,
       "Humidity":  29.100597381591797,
       "pressure":  28.883883226248145,
       "air_quality":   37.067466735839844,
       "SoilMoisture":  0.42462845010615713,
       "Lat":   0,
       "Long":  0,
       "Alt":   0
    }
}
'
AS NVARCHAR(MAX)
) as myjsoncolumn
UNION ALL
SELECT 5,6,'Test','
{
    "device":   {
        "battery_level":    2,
        "rssi": -105,
        "boot_cnt": 5,
        "apn":  "teal"
    },
     "data":    {
       "Humidity":  29.100597381591797,
       "pressure":  28.883883226248145
    }
}
'
)

SELECT *, 
JSON_VALUE(myjsoncolumn,'$.device.battery_level') as battery_level,
JSON_VALUE(myjsoncolumn,'$.data.Temperature') as Temp
FROM MySampleTable

How do I point to a column in a table though, which has many rows of json strings? — RalphBiggerton, Jul 21 '21 at 05:51
The sample works for a table with many rows. I will edit it but note that this is something that you could try yourself. — Nick.Mc, Jul 21 '21 at 05:52
I've edited the answer to return data from a multi row table. — Nick.Mc, Jul 21 '21 at 06:03

Zhorov · Answer 2 · 2021-07-21T06:09:50.887

The statement usually depends on the structure of the parsed JSON data. In this case, a possible option is OPENJSON() with explicit schema (the WITH clause with columns definitions), using the appropriate data types and path expressions:

JSON and table:

DECLARE @json varchar(max) = '{
    "device":   {
        "battery_level":    98,
        "rssi": -105,
        "boot_cnt": 5,
        "apn":  "teal",
        "ip_addr":  "10.176.30.171",
        "fw_ver":   "1.00",
        "modem_fw": "mfw_nrf9160_1.3.0",
        "imsi": "234500024531391",
        "imei": "352656101040510",
        "iccid":    "8901990000000534985"
    },
     "data":    {
       "Temperature":   77.563942718505871,
       "Humidity":  29.100597381591797,
       "pressure":  28.883883226248145,
       "air_quality":   37.067466735839844,
       "SoilMoisture":  0.42462845010615713,
       "Lat":   0,
       "Long":  0,
       "Alt":   0
    }
}'
SELECT *
INTO Data
FROM (VALUES
   ('011', '015', '2021-07-20', '015', @json),
   ('012', '016', '2021-08-20', '016', @json)
) v (ID, ORGANIZATION, DEVICE_TIME, DEVICE, DATA)

Statement:

SELECT d.*, j.*
FROM Data d
OUTER APPLY OPENJSON(d.DATA) WITH (
   batery_level int '$.device.battery_level',
   rssi int '$.device.rssi',
   boot_cnt int '$.device.boot_cnt',
   apn varchar(10) '$.device.boot_apn',
   ip_addr varchar(19) '$.device.ip_addr',
   fw_ver varchar(5) '$.device.fw_ver',
   modem_fw varchar(59) '$.device.modem_fw',
   imsi varchar(15) '$.device.imsi',
   imei varchar(15) '$.device.imei',
   iccid varchar(50) '$.device.iccid',
   Temperature numeric(20, 15) '$.data.Temperature',
   Humidity numeric(22, 18) '$.data.Humidity',
   pressure numeric(22, 18) '$.data.pressure',
   air_quality numeric(22, 18) '$.data.air_quality',
   SoilMoisture numeric(22, 18) '$.data.SoilMoisture',
   Lat numeric(9, 6) '$.data.Lat',
   Long numeric(9, 6) '$.data.Long',
   Alt numeric(9, 6) '$.data.Alt'
) j

Parsing JSON from SQL Server

2 Answers2