I am trying to read the following information taken from a nested json file in SQL Server:

Declare @json nvarchar(max)
SELECT @json =
N'{
  "Energy": 
  {
    "Energy-X/A": [
      [
        100.123456, null
      ],
      [
        101.123456, null
      ]
    ],
    "Energy-X/B": [
      [
        102.123456, null
      ],
      [
        103.123456, null
      ]
    ]
  }
}'
select 
    JSON_VALUE(a.value, '$.energy.Energy-X/A') as [Energy-X/A],
    JSON_VALUE(b.value, '$.energy.Energy-X/B') as [Energy-X/B]
from OPENJSON(@json, '$.Energy') as a
CROSS APPLY OPENJSON(a.value, '$.energy') as b

The expected output should be that there are two columns with two entries for each row:

Energy-X/A

100.123456, null

101.123456, null

Energy-X/B

102.123456, null

103.123456, null

However, I am encountering two problems which I could not figure out:

1. If I execute the SQL statement nothing happens which indicates that I am obviously doing something wrong.
2. I have almost 1000 entries like "Energy-X/A", "Energy-X/B", "Energy-X/AC", etc.. Is there any better approach to extract the information without reusing the "JSON_VALUE()" function and introducing, e.g., b. value?

I am grateful for any help!

CodePudding user response：

SQL Server won't return the data quite like you're expecting. json_value() is used to return scalar values such as strings, numbers and booleans, but when you want to return an array (or array of arrays) or objects you can use json_query() to return a snippet of the JSON data. For example:

select 
    json_query(Energy, '$."Energy-X/A"') as [Energy-X/A],
    json_query(Energy, '$."Energy-X/B"') as [Energy-X/B]
from openjson (@json) with (
  Energy nvarchar(max) as json
) as a;

Returns the output:

Energy-X/A               Energy-X/B
------------------------ ----------------------------
[                        [
      [                        [
        100.123456, null         102.123456, null
      ],                       ],
      [                        [
        101.123456, null         103.123456, null
      ]                        ]
    ]                        ]

The query could be simplified to the following, which yields the same result:

select 
    json_query(@json, '$.Energy."Energy-X/A"') as [Energy-X/A],
    json_query(@json, '$.Energy."Energy-X/B"') as [Energy-X/B];

Edit...

If the JSON snippets of Energy-X/A and Energy-X/B are not sufficient for your purpose then you need to know their structure and parse them manually.

If they will consistently be 2 element arrays of 2 element arrays and you want to produce a row for each outer array that contains a comma-delimited list of the inner array's elements you can use a query such as the following (note: this requires SQL Server 2017 or later to make use of the string_agg() function):

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]);

Which produces the results...