Reading nested JSON returns NULL-CodePudding

I have looked at multiple answers that seemed similar but couldn't find an answer. Currently I am working with this data:

[{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[10.74, 50.67], [7.75, 5.21]]}, "HQ": "X", "City": "X"},  
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[11.66, 49.55], [1.73, 5.05]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[12.91, 50.98], [2.72, 8.29]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[4.65, 50.39], [3.53, 6.29]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[4.48, 50.32], [4.42, 4.79]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[6.05, 49.06], [5.99, 4.51]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[6.65, 50.41], [6.77, 5.94]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[6.65, 50.41], [7.77, 4.94]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[6.74, 50.73], [8.72, 3.23]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[9.65, 50.19], [9.26, 5.68]]}, "HQ": "X", "City": "X"}]

After parsing it using:

declare @data nvarchar(max) = (SELECT *  FROM [dbo].[Temp_Database_01] for json path)

select *
from  
   openjson(@data) 
   with (trade_area nvarchar(max) '$.Trade_Area')

I have obtained the following:

                                       trade_area
  {"type": "Polygon", "coordinates": [[10.74, ...
  {"type": "Polygon", "coordinates": [[11.66, ...
  {"type": "Polygon", "coordinates": [[2.91,  ...
  {"type": "Polygon", "coordinates": [[4.65,  ...
  {"type": "Polygon", "coordinates": [[4.48,  ...
  {"type": "Polygon", "coordinates": [[6.05, 4...
  {"type": "Polygon", "coordinates": [[6.65,  ...
  {"type": "Polygon", "coordinates": [[6.65,  ...
  {"type": "Polygon", "coordinates": [[6.74,  ...
  {"type": "Polygon", "coordinates": [[9.65,  ...

Now, I want dive deeper into the JSON and access the Trade_Area by trying to get the type value which is Polygon for this sample data by using:

declare @data nvarchar(max) = (SELECT *  FROM [dbo].[Temp_Database_01] for json path)

select *
from  
   openjson(@data) 
   with (trade_area nvarchar(max) '$.Trade_Area.type')

However, I am getting NULL for all rows. What am I doing wrong? My ideal output would be to get both values split into two columns:

      type                      coordinates
0  Polygon  [[10.74, 50.67], [7.75, 5.21]]
1  Polygon  [[11.66, 49.55], [1.73, 5.05]]
2  Polygon   [[12.91, 50.98], [2.72, 8.29]]
3  Polygon   [[4.65, 50.39], [3.53, 6.29]]
4  Polygon  [[4.48, 50.32], [4.48, 4.79]]
5  Polygon   [[6.05, 49.06], [5.05, 4.51]]
6  Polygon  [[6.65, 50.41], [6.65, 5.94]]
7  Polygon  [[6.65, 50.41], [7.65, 4.94]]
8  Polygon   [[6.74, 50.73], [8.72, 3.23]]
9  Polygon  [[9.65, 50.19], [9.26, 5.68]]

CodePudding user response：

This

declare @data nvarchar(max) = '

[{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[10.74, 50.67], [7.75, 5.21]]}, "HQ": "X", "City": "X"},  
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[11.66, 49.55], [1.73, 5.05]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[12.91, 50.98], [2.72, 8.29]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[4.65, 50.39], [3.53, 6.29]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[4.48, 50.32], [4.42, 4.79]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[6.05, 49.06], [5.99, 4.51]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[6.65, 50.41], [6.77, 5.94]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[6.65, 50.41], [7.77, 4.94]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[6.74, 50.73], [8.72, 3.23]]}, "HQ": "X", "City": "X"}, 
{"Lat": "X", "Long": "X", "Trade_Area": {"type": "Polygon", "coordinates": [[9.65, 50.19], [9.26, 5.68]]}, "HQ": "X", "City": "X"}]
'

select *
from openjson(@data)
with 
(
  type nvarchar(100) '$.Trade_Area.type',
  coordinates nvarchar(max) '$.Trade_Area.coordinates' as json
) as t

outputs

type             coordinates
---------------- -----------------------------------
Polygon          [[10.74, 50.67], [7.75, 5.21]]
Polygon          [[11.66, 49.55], [1.73, 5.05]]
Polygon          [[12.91, 50.98], [2.72, 8.29]]
Polygon          [[4.65, 50.39], [3.53, 6.29]]
Polygon          [[4.48, 50.32], [4.42, 4.79]]
Polygon          [[6.05, 49.06], [5.99, 4.51]]
Polygon          [[6.65, 50.41], [6.77, 5.94]]
Polygon          [[6.65, 50.41], [7.77, 4.94]]
Polygon          [[6.74, 50.73], [8.72, 3.23]]
Polygon          [[9.65, 50.19], [9.26, 5.68]]

CodePudding user response：

Just a slight twist if you want the key (sequence)

select A.[key]
      ,B.[Type]
      ,B.coordinates
from   openjson(@data) A
Cross Apply openjson(A.Value) with ( type        nvarchar(100) '$.Trade_Area.type'
                                    ,coordinates nvarchar(max) '$.Trade_Area.coordinates' as json
                                   ) B

Results