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