Remove elements from a JSON array, with no key

I'm looking for a way that I can remove elements from a JSON array in SQL Server, I tried using JSON_MODIFY/OPENPATH, but I can't seem to get the path parameter correct.

Here is an example of what I want to do is convert:

[{"FieldName":"OrderStatusTypeId","FieldType":"Int32","ValueBefore":"8","ValueAfter":"10","Action":2},{"FieldName":"PatientReferenceNumber","FieldType":"String","ValueBefore":"","ValueAfter":"","Action":2}]

To be:

[{"FieldName":"OrderStatusTypeId","FieldType":"Int32","ValueBefore":"8","ValueAfter":"10","Action":2}]

Basically, I want to remove any element of the array, whereby the Action is 2 and the ValueBefore and the ValueAfter fields are the same.

Here is what I'm attempting at the moment, as a test, but I keep getting the error below:

DECLARE @JSONData AS NVARCHAR(4000)  
SET @JSONData = N'[{"FieldName":"OrderStatusTypeId","FieldType":"Int32","ValueBefore":"8","ValueAfter":"10","Action":2},{"FieldName":"PatientReferenceNumber","FieldType":"String","ValueBefore":"","ValueAfter":"","Action":2},{"FieldName":"PoNumber","FieldType":"String","ValueBefore":"","ValueAfter":"","Action":2},{"FieldName":"ReferringPhysicianName","FieldType":"String","ValueBefore":"","ValueAfter":"","Action":2},{"FieldName":"InsuranceProvider","FieldType":"String","ValueBefore":"","ValueAfter":"","Action":2},{"FieldName":"TreatmentId","FieldType":"String","ValueBefore":"","ValueAfter":"","Action":2},{"FieldName":"RowVersion[6]","FieldType":"Byte","ValueBefore":"10","ValueAfter":"115","Action":2},{"FieldName":"OrderStatusType.Id","FieldType":"Int32","ValueBefore":"8","ValueAfter":"10","Action":2},{"FieldName":"OrderStatusType.Description","FieldType":"String","ValueBefore":"Delivered","ValueAfter":"Failed Logistics","Action":2},{"FieldName":"OrderStatusType.IsSelectable","FieldType":"Boolean","ValueBefore":"False","ValueAfter":"True","Action":2}]'

SELECT @JSONData = JSON_MODIFY(@JSONData, '$',
   JSON_QUERY(
      (
      SELECT *
      FROM OPENJSON(@JSONData, '$') WITH (
         FieldName nvarchar(1000) '$.FieldName',
         FieldType nvarchar(1000) '$.FieldType',
         ValueBefore nvarchar(1000) '$.ValueBefore',
         ValueAfter nvarchar(1000) '$.ValueAfter',
         Action int '$.Action'
      )
      WHERE Action <> 2 AND ValueBefore <> ValueAfter
      FOR JSON PATH
      )
   )
)

Error:

Msg 13619, Level 16, State 1, Line 4
Unsupported JSON path found in argument 2 of JSON_MODIFY.

The expected output I'm looking for is:

[{"FieldName":"OrderStatusTypeId","FieldType":"Int32","ValueBefore":"8","ValueAfter":"10","Action":2},{"FieldName":"RowVersion[6]","FieldType":"Byte","ValueBefore":"10","ValueAfter":"115","Action":2},{"FieldName":"OrderStatusType.Id","FieldType":"Int32","ValueBefore":"8","ValueAfter":"10","Action":2},{"FieldName":"OrderStatusType.Description","FieldType":"String","ValueBefore":"Delivered","ValueAfter":"Failed Logistics","Action":2},{"FieldName":"OrderStatusType.IsSelectable","FieldType":"Boolean","ValueBefore":"False","ValueAfter":"True","Action":2}]

How can I work out the correct JSON Path value, all the examples I seem to find online don't have an array as the root element of the JSON string.

Note, the order of the elements isn't important.

Solution

You don't have to use JSON_MODIFY here. You can just select the data you want as a table, filter it, then re-encode it as JSON.

DECLARE @JSONData AS NVARCHAR(4000)  
SET @JSONData = N'[{"FieldName":"OrderStatusTypeId","FieldType":"Int32","ValueBefore":"8","ValueAfter":"10","Action":2},{"FieldName":"PatientReferenceNumber","FieldType":"String","ValueBefore":"","ValueAfter":"","Action":2},{"FieldName":"PoNumber","FieldType":"String","ValueBefore":"","ValueAfter":"","Action":2},{"FieldName":"ReferringPhysicianName","FieldType":"String","ValueBefore":"","ValueAfter":"","Action":2},{"FieldName":"InsuranceProvider","FieldType":"String","ValueBefore":"","ValueAfter":"","Action":2},{"FieldName":"TreatmentId","FieldType":"String","ValueBefore":"","ValueAfter":"","Action":2},{"FieldName":"RowVersion[6]","FieldType":"Byte","ValueBefore":"10","ValueAfter":"115","Action":2},{"FieldName":"OrderStatusType.Id","FieldType":"Int32","ValueBefore":"8","ValueAfter":"10","Action":2},{"FieldName":"OrderStatusType.Description","FieldType":"String","ValueBefore":"Delivered","ValueAfter":"Failed Logistics","Action":2},{"FieldName":"OrderStatusType.IsSelectable","FieldType":"Boolean","ValueBefore":"False","ValueAfter":"True","Action":2}]'

set @JSONData = 
(
    SELECT *
    FROM OPENJSON(@JSONData, '$') WITH (
        FieldName nvarchar(1000) '$.FieldName',
        FieldType nvarchar(1000) '$.FieldType',
        ValueBefore nvarchar(1000) '$.ValueBefore',
        ValueAfter nvarchar(1000) '$.ValueAfter',
        Action int '$.Action'
    )
    WHERE not (Action = 2 and ValueBefore = ValueAfter)
    FOR JSON PATH
)

and the JSON is

[
    {
        "FieldName": "OrderStatusTypeId",
        "FieldType": "Int32",
        "ValueBefore": "8",
        "ValueAfter": "10",
        "Action": 2
    },
    {
        "FieldName": "RowVersion[6]",
        "FieldType": "Byte",
        "ValueBefore": "10",
        "ValueAfter": "115",
        "Action": 2
    },
    {
        "FieldName": "OrderStatusType.Id",
        "FieldType": "Int32",
        "ValueBefore": "8",
        "ValueAfter": "10",
        "Action": 2
    },
    {
        "FieldName": "OrderStatusType.Description",
        "FieldType": "String",
        "ValueBefore": "Delivered",
        "ValueAfter": "Failed Logistics",
        "Action": 2
    },
    {
        "FieldName": "OrderStatusType.IsSelectable",
        "FieldType": "Boolean",
        "ValueBefore": "False",
        "ValueAfter": "True",
        "Action": 2
    }
]