Remove elements from a JSON array, with no key

Question

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.

score 2 · Accepted Answer · answered Nov 08 '20 at 21:50

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

Remove elements from a JSON array, with no key

1 Answers1