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

Question

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

Will each key have the same number of values in their arrays? — griv
– griv, Commented Jan 2, 2023 at 1:36

griv · Accepted Answer · 2023-01-02 02:53:30Z

2

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.

answered Jan 2, 2023 at 2:53

griv

2,2852 gold badges16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Cappelletti · Accepted Answer · 2023-01-02 06:01:34Z

1

Already +1 on griv's answer ... it was my first thought as well.

However, and just for fun, I wanted to try an alternative which ODDLY enough had a lower batch cost 40% vs 60% (image below)

I should also note that 2016 requires a literal while 2017+ can be an expression

Example

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

Select ReportingUnit = JSON_VALUE(@json,concat('$."reporting.unit"[',N,']'))
      ,NotionalLC    = JSON_VALUE(@json,concat('$."notional.lc"[',N,']'))
 From ( Select Top 25 N=-1+Row_Number() Over (Order By (Select NULL)) 
          From master..spt_values n1
      ) A
 Where JSON_VALUE(@json,concat('$."reporting.unit"[',N,']')) is not null

Results

ReportingUnit   NotionalLC
F-1             100.1
F-2             140.2
F-3             150.3

Image

answered Jan 2, 2023 at 6:01

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

2 Comments

griv Over a year ago

Interesting answer. What tool are you using to visualize/calculate the batch?

John Cappelletti Over a year ago

@griv In SS Management Studio, just click on the toolbar button [Display Estimated Execution Plan] or Cntrl+L

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related