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!
