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

Answer 1

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