sql-server - SQL Server：JSON - 按数组内数组中的值过滤

Question

我有一个包含复杂 json 列的表：

{
   "Workflow":{
      "Waypoints":[
         {
            "Name":"Test",
            "Events":[
               {
                  "Id":"1",
                  "OriginId":"1",
                  "Position":"TopLeft",
                  "ScanType":"Scan",
                  "ScanOrder":0
               }
            ]
         },
         {
            "Name":"Test2",
            "Events":[
               {
                  "Id":"2",
                  "OriginId":"2",
                  "Position":"TopLeft",
                  "ScanType":"UnitScan",
                  "ScanOrder":1
               }
            ]
         }
      ]
   }
}

我需要EventId按位置和 ScanOrder 接收。

我尝试了这样的解决方案，但它不起作用：

SELECT * 
FROM OPENJSON('@json', '$.Workflow.Waypoints.Events')
     WITH (Id NVARCHAR(32) '$.Id') A

但它没有返回任何记录。

标签： sql-server

Answer 1

您可以像这样解析 JSON：

/* JSON data */
DECLARE @json nvarchar(MAX) = '{
   "Workflow":{
      "Waypoints":[
         {
            "Name":"Test",
            "Events":[
               {
                  "Id":"1",
                  "OriginId":"1",
                  "Position":"TopLeft",
                  "ScanType":"Scan",
                  "ScanOrder":0
               }
            ]
         },
         {
            "Name":"Test2",
            "Events":[
               {
                  "Id":"2",
                  "OriginId":"2",
                  "Position":"TopLeft",
                  "ScanType":"UnitScan",
                  "ScanOrder":1
               }
            ]
         }
      ]
   }
}';

/* Parse JSON */
SELECT
    [Name], Id, OriginId, Position, ScanType, ScanOrder
FROM OPENJSON ( @json, '$.Workflow.Waypoints' ) WITH (
    [Name] varchar(50) '$.Name',
    [Events] nvarchar(MAX) '$.Events' AS JSON
)
CROSS APPLY OPENJSON ( [Events] ) WITH (
    Id int '$.Id',
    OriginId int '$.OriginId',
    Position varchar(50) '$.Position',
    ScanType varchar(50) '$.ScanType',
    ScanOrder int '$.ScanOrder'
);

退货

+-------+----+----------+----------+----------+-----------+
| Name  | Id | OriginId | Position | ScanType | ScanOrder |
+-------+----+----------+----------+----------+-----------+
| Test  |  1 |        1 | TopLeft  | Scan     |         0 |
| Test2 |  2 |        2 | TopLeft  | UnitScan |         1 |
+-------+----+----------+----------+----------+-----------+

你可以添加一个你认为合适的WHERE和。ORDER BY