How to exclude multiple values from JSON String in MS SQL Stored Procedure-CodePudding

I am having a JSON string like below:

{
  "Country": {
    "Layer4": [
      {
        "ItemName": "Cabinet MT",
        "ItemId": "cc3b0435-9ff5-4fd8-9f49-e049919a1414"
      },
      {
        "ItemName": "Other MT",
        "ItemId": "cc3b0435-9ff5-4fd8-9f49-e049919a1414"
      },
      {
        "ItemName": "Cold MT",
        "ItemId": "cc3b0435-9ff5-4fd8-9f49-e049919a1414"
      },
      {
        "ItemName": "Cold MT",
        "ItemId": "672f9a8c-71bb-4851-87de-e68154cabfad"
      },
      {
        "ItemName": "Cabinet MT",
        "ItemId": "672f9a8c-71bb-4851-87de-e68154cabfad"
      }
    ]
  },
  "CountryID": "b4283692-7c14-46da-9480-9a2976187316"
}

I want to remove data under Layer4 whose ItemName = 'Cabinet MT' and ItemName = 'Other MT' and ItemId = 'cc3b0435-9ff5-4fd8-9f49-e049919a1414' and finally I want JSON string to look like this:

{
  "Country": {
    "Layer4": [
      {
        "ItemName": "Cold MT",
        "ItemId": "cc3b0435-9ff5-4fd8-9f49-e049919a1414"
      },
      {
        "ItemName": "Cold MT",
        "ItemId": "672f9a8c-71bb-4851-87de-e68154cabfad"
      },
      {
        "ItemName": "Cabinet MT",
        "ItemId": "672f9a8c-71bb-4851-87de-e68154cabfad"
      }
    ]
  },
  "CountryID": "b4283692-7c14-46da-9480-9a2976187316"
}

I have tried in below format:

Declare @Input NVARCHAR(MAX) = N'{"Country":{"Layer4":[{"ItemName":"Cabinet MT","ItemId":"cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName":"Other MT","ItemId":"cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName":"Cold MT","ItemId":"cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName":"Cold MT","ItemId":"672f9a8c-71bb-4851-87de-e68154cabfad"},{"ItemName":"Cabinet MT","ItemId":"672f9a8c-71bb-4851-87de-e68154cabfad"}]},"CountryID":"b4283692-7c14-46da-9480-9a2976187316"}';
DECLARE @JSONOutput AS NVARCHAR(MAX);
DECLARE @JSONData AS NVARCHAR(MAX);

SET @JSONData = @Input; 

            SELECT @JSONOutput = JSON_MODIFY(@JSONData, '$.Country.Layer4', JSON_QUERY('[]'))
            SELECT @JSONOutput = JSON_MODIFY(@JSONOutput, 'append $.Country.Layer4', JSON_QUERY(@JSONData, '$.Country.Layer4['   [key]   ']'))
            FROM OPENJSON(@JSONData, '$.Country.Layer4')
            WHERE JSON_VALUE([value], '$.ItemName') NOT IN('Cabinet MT', 'Other MT')
            and JSON_VALUE([value], '$.ItemId') NOT IN ('cc3b0435-9ff5-4fd8-9f49-e049919a1414')

            Print @JSONOutput

and I am getting output as:

{
  "Country": {
    "Layer4": [
      {
        "ItemName": "Cold MT",
        "ItemId": "672f9a8c-71bb-4851-87de-e68154cabfad"
      }
    ]
  },
  "CountryID": "b4283692-7c14-46da-9480-9a2976187316"
}

Could some one help me to remove only specific values which satisfy my condition

CodePudding user response：

It seems like a wrong boolean logic, try to use a different WHERE clause:

...
WHERE NOT (
   (JSON_VALUE([value], '$.ItemName') = N'Other MT') AND (JSON_VALUE([value], '$.ItemId') = N'cc3b0435-9ff5-4fd8-9f49-e049919a1414') OR
   (JSON_VALUE([value], '$.ItemName') = N'Cabinet MT') AND (JSON_VALUE([value], '$.ItemId') = N'cc3b0435-9ff5-4fd8-9f49-e049919a1414')
)

An important note (as is explained in the BOL): Don't use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur because all expressions in the SELECT list (including assignments) aren't necessarily run exactly once for each output row.

A possible approach is to parse, filter and rebuild the input JSON:

SELECT @JSONOutput = JSON_MODIFY (
   @Input,
   '$.Country.Layer4',
   (
   SELECT * 
   FROM OPENJSON(@Input, '$.Country.Layer4') WITH (
      ItemName nvarchar(100) '$.ItemName',
      ItemId nvarchar(36) '$.ItemId'
   )  
   WHERE NOT (
      (ItemName = N'Other MT') AND (ItemId = N'cc3b0435-9ff5-4fd8-9f49-e049919a1414') OR
      (ItemName = N'Cabinet MT') AND (ItemId = N'cc3b0435-9ff5-4fd8-9f49-e049919a1414')
   )  
   FOR JSON PATH
   )
)