Read in nested JSON file in SQL Server

Question

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:

If I execute the SQL statement nothing happens which indicates that I am obviously doing something wrong.
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!

What's your expected output? Energy-X/A and Energy-X/B aren't standard Javascript tokens (and JSON is Javascript Object Notation), so you'll need to quote them in your JSON queries, e.g.: '$."Energy-X/A"'. Also JSON, as with Javascript, is case-sensitive so '$.energy' won't match anything in the sample data. — AlwaysLearning
– AlwaysLearning, Commented Aug 13, 2022 at 11:15
Please Edit your question to show your expected output. "Energy-X/A" and "Energy-X/B" are arrays of arrays, so it's not immediately clear what you mean when they say they should include two entries. — AlwaysLearning
– AlwaysLearning, Commented Aug 13, 2022 at 13:05
Thanks for the hint @AlwaysLearning! Each column "Energy-X/A" and "Energy-X/B" should include 2 entries. I have re-written the SQL query by removing the "CROSS APPLY" part but now I get "NULL" entries if I want to retrieve the columns like this: select JSON_VALUE(a.value, '$."Energy-X/A"') as [Energy-X/A], JSON_VALUE(a.value, '$."Energy-X/B"') as [Energy-X/B] from OPENJSON(@json, '$."Energy"') as a — DaveS
– DaveS, Commented Aug 13, 2022 at 13:06
Your expected result is confusing. Can you show a proper table as the expected output? — Charlieface
– Charlieface, Commented Aug 14, 2022 at 2:05

AlwaysLearning · Accepted Answer · 2022-08-14 00:47:07Z

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

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

Great! What about the second question? Is there any better approach instead of hardcoding the problem by repeating the json_query()-funtion? In order to pull out the "float values" and "null" ones I need to use cross apply, am I right? Could you please show how this would look like?

Collectives™ on Stack Overflow

Read in nested JSON file in SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related