Nested SELECT statements and reading in nested JSON file in SQL Server

Question

The discussed problem has been solved partly in here: Read in nested JSON file in SQL Server

but now the JSON file was extended with more objects of different format.

Declare @json nvarchar(max)
SELECT @json =
N'{
  "Model": {
    "Energy-X/A": {
      "x": 1,
      "y": 2,
      "z": 3
    },
    "Energy-X/B": {
      "x": 4,
      "y": 5,
      "z": 6
    }
    },
  "Energy": 
  {
    "Energy-X/A": [
      [
        100.123456, null
      ],
      [
        101.123456, null
      ]
    ],
    "Energy-X/B": [
      [
        102.123456, null
      ],
      [
        103.123456, null
      ]
    ]
  }
}'

select * from openjson(@json, '$.Model')
with (x [int] '$."Energy-X/A".x',
      y [int] '$."Energy-X/A".y',
      z [int] '$."Energy-X/A".z',
      x [int] '$."Energy-X/B".x',
      y [int] '$."Energy-X/B".y',
      z [int] '$."Energy-X/B".z'
      );

select commaDelimited.* from openjson (@json) 
with (energyXA nvarchar(max) '$.Energy."Energy-X/A"' as json,
      energyXB nvarchar(max) '$.Energy."Energy-X/B"' as json
     ) as energy
    cross apply (
    select
    (select string_agg(isnull(value, 'null'), ',') from openjson(energyXA, '$[0]')),
    (select string_agg(isnull(value, 'null'), ',') from openjson(energyXB, '$[0]'))
    union all
    select
    (select string_agg(isnull(value, 'null'), ',') from openjson(energyXA, '$[1]')),
    (select string_agg(isnull(value, 'null'), ',') from openjson(energyXB, '$[1]'))
)   commaDelimited ([Energy-X/A], [Energy-X/B]);

The solution works and the values can be extracted but now I want to combine both SELECT statements into one query and construct a correlated subquery. The columns should appear when "Energy-X/A" and Energy-X/B" match like:

Energy-X/A	Energy-X/A	x	y	z
100.123456, null	101.123456, null	1	2	3

Energy-X/B	Energy-X/B	x	y	z
102.123456, null	103.123456, null	4	5	6

or also better output would be to sum up the values of Energy-X/A and Energy-X/B in one, separate column (using a delimiter such as semicolon):

Energy-X/A	x	y	z
100.123456, null ; 101.123456, null	1	2	3

Energy-X/B	x	y	z
102.123456, null ; 103.123456, null	1	2	3

I am grateful for any help!

What do you mean by ... The columns with the corresponding values? Do you mean that "Model" matches "Energy-X/A"? — Zhorov
– Zhorov, Commented Aug 16, 2022 at 14:02
Thanks for the hint @Zhorov. I just edited the answer with an examplary result. — DaveS
– DaveS, Commented Aug 16, 2022 at 14:58

Charlieface · Accepted Answer · 2022-08-16 16:27:06Z

2

Since you changed your expected results significantly, I've completely re-written your query.

Start by unpivoting the A and B values into separate rows using a (values) table and json_query.

Then read those columns using openjson.

In the case of Energy you need two levels of aggregation also, in order to get your second expected result.

select
  commaDelimited.*,
  model.*
from (values
    (json_query(@json, '$.Model.BCS'),  json_query(@json, '$.Energy."Energy-X/A"')),
    (json_query(@json, '$.Model.BCSA'), json_query(@json, '$.Energy."Energy-X/B"'))
) j(model, energy)
outer apply openjson(j.model)
  with (
    x int,
    y int,
    z int
  ) model
outer apply (
    select
      Energy = string_agg(c.Energy, ' ; ')
    from openjson(j.energy) energy
    cross apply (
        select
          Energy = string_agg(isnull(Xinner.value, 'null'), ', ')
        from openjson(energy.value) Xinner
    ) c
) commaDelimited;

db<>fiddle

edited Aug 16, 2022 at 16:27

answered Aug 16, 2022 at 14:54

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

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

9 Comments

DaveS Over a year ago

Thanks @Charlieface for the elegant solution. Unfortunately, while you posted your answer I was editing my question so that the expected result has changed a bit

Charlieface Over a year ago

See latest edit

DaveS Over a year ago

Perfect! I have not thought about the combined usage of "outer" and "cross apply", thank you very much!

Charlieface Over a year ago

outer is just a left-join, as opposed to cross which is an inner join. Depends on your data whether you need it

Charlieface Over a year ago

You could just add it as a literal string inside the values unpivot dbfiddle.uk/…

|

Collectives™ on Stack Overflow

Nested SELECT statements and reading in nested JSON file in SQL Server

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related