讀取嵌套的JSON回傳NULL

我查看了多個看似相似但找不到答案的答案。目前我正在處理這些資料：

[{"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"}]

使用決議后：

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

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

我獲得了以下資訊：

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

現在，我想更深入地研究 JSON 并Trade_Area通過嘗試使用以下方法獲取type此示例資料的 Polygon 值來訪問 JSON ：

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')

但是，我正在獲取NULL所有行。我究竟做錯了什么？我的理想輸出是將兩個值分成兩列：

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

uj5u.com熱心網友回復：

如果你想要鑰匙（序列），只需稍微扭曲一下

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

結果

uj5u.com熱心網友回復：

這個

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

輸出

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

標籤：sql json sql-server 查询语句

上一篇：回傳串列中名字以特定值開頭的物件（jmespath）

下一篇：如何從JSON中檢索獲取資料？