Search code examples
sql-serveropen-json

Use openjson to get columns from JSON with multiple arrays of elements


JSON input looks like this:

{
"reporting.unit": [ "F-1", "F-2", "F-3"],
"notional.lc": [  100.1, 140.2, 150.3]
}

Desired Output:

reporting.unit notional.lc
F-1 100.1
F-2 140.2
F-3 150.3

Note I have upwards of 20 columns and many more elements

I tried:

    DECLARE @json nvarchar(max);
    SELECT @json = '{
    "reporting.unit": [ "F-1", "F-2", "F-3"],
    "notional.lc": [  100.1, 140.2, 150.3]
    }';
    SELECT *
    FROM OPENJSON (@json);

but the result was:

key value type
reporting.unit [ "F-1", "F-2", "F-3"] 4
notional.lc [ 100.1, 140.2, 150.3] 4

Solution

  • You can use OPENJSON with multiple JOIN's to join your columns together using your array keys to get the values in a column/row format.

    DECLARE @json nvarchar(max);
    SELECT @json = '{
    "reporting.unit": [ "F-1", "F-2", "F-3"],
    "notional.lc": [  100.1, 140.2, 150.3]
    }';
    SELECT 
      a.value AS [reporting.unit],
      b.value AS [notional.lc]
    FROM OPENJSON(@json, '$."reporting.unit"') a
    JOIN OPENJSON(@json, '$."notional.lc"') b 
    ON a.[key] = b.[key]
    

    Result:

    reporting.unit notional.lc
    F-1 100.1
    F-2 140.2
    F-3 150.3

    Demo here.