How to remove particular entry by adding multiple condition in array using N1QL

SQL++
1

Collection:

{
  "answerVersionsArray": [
    {
      "answerId": 2,
      "answerMeaningId": "2.01",
      "createdBy": "Ramadoss E",
      "createdOn": "2021-09-12T05:27:49.8898031Z",
      "isDeleted": false,
      "questionId": 2,
      "tagId": 1,
      "tagName": "dev-tag-1",
    },
    {
      "answerId": 2,
      "answerMeaningId": "2.01",
      "createdBy": "Ramadoss E",
      "createdOn": "2021-09-12T05:27:49.9346289Z",
      "isDeleted": false,
      "questionId": 2,
      "tagId": 2,
      "tagName": "dev-tag-2"
    },
    {
      "answerId": 2,
      "answerMeaningId": "2.01",
      "createdBy": "Ramadoss E",
      "createdOn": "2021-09-12T05:27:49.9842107Z",
      "isDeleted": false,
      "questionId": 2,
      "tagId": 3,
      "tagName": "dev-tag-3"
    },
    {
      "answerId": 3,
      "answerMeaningId": "3.01",
      "createdBy": "Ramadoss E",
      "createdOn": "2021-09-12T05:27:49.9346289Z",
      "isDeleted": false,
      "questionId": 3,
      "tagId": 2,
      "tagName": "dev-tag-2"
    }
  ],
  "createdBy": "Ramadoss E",
  "createdOn": "2021-09-12T05:27:18.2214465Z",
  "id": "229b7622-a5b9-4773-ae6e-ec392ede7509",
  "isActive": true,
  "type": "answersCollection"
}

I want to remove only one entry from the array based on answerId, answerMeaningId, questionId and tagId

 {
      "answerId": 2,
      "answerMeaningId": "2.01",
      "createdBy": "Ramadoss E",
      "createdOn": "2021-09-12T05:27:49.8898031Z",
      "isDeleted": false,
      "questionId": 2,
      "tagId": 1,
      "tagName": "dev-tag-1",
    }

My query:

UPDATE formsmanager AS fm SET fm.answerVersionsArray = ARRAY v FOR v IN fm.answerVersionsArray WHEN v.questionId = 2 AND v.answerId != 2 AND v.answerMeaningId != '2.01' AND v.tagId != 1 END, fm.modifiedOn = CLOCK_UTC(), fm.modifiedBy = 'RamadossE' WHERE fm.type = 'answersCollection'"

Problem:
The above N1QL query removing all the records in the array.

Could you please any one help me this?

2

Reconstructing ARRAY by removing matching condition, so need reverse condition, As negative condition you need to consider MISSING, NULL values (condition) to include not remove.

UPDATE formsmanager AS fm 
SET fm.answerVersionsArray = ARRAY v 
                             FOR v IN fm.answerVersionsArray 
                             WHEN IFMISSINGORNULL(NOT (v.questionId = 2 AND v.answerId = 2 AND v.answerMeaningId != "2.01" AND v.tagId != 1) , true)
                             END, 
    fm.modifiedOn = CLOCK_UTC(),
    fm.modifiedBy = "RamadossE"
WHERE fm.type = "answersCollection"
1 Like
3

Thanks vsr1, I have tried the solutions but it’s not removing any records.

4

I notice in your example document you don’t have the “modifiedBy” or “modifiedOn” fields. Did you mean “createdBy” and “createdOn” ?

(And you don’t have an element that matches the conditions questionId = 2 + answerId = 2 + answerMeaningId != ‘2.01’ and tag != 1 in the example. )

Furthermore, CLOCK_UTC() is going to return the time the update statement is run - so unlikely to match any record.

If you provided the actual time for a record it should match; this along with flipping the condition to "answerMeaningId = ‘2.01’ , @vsr1 's statement becomes:

UPDATE formsmanager AS fm 
SET fm.answerVersionsArray = ARRAY v 
                             FOR v IN fm.answerVersionsArray 
                             WHEN IFMISSINGORNULL(NOT (v.questionId = 2 AND v.answerId = 2 AND v.answerMeaningId = "2.01" AND v.tagId != 1) , true)
                             END, 
    fm.createdOn = ""2021-09-13T08:11:00.754Z"
    fm.createdBy = "RamadossE"
WHERE fm.type = "answersCollection"

and this removes two elements when I test:

                {
                    "answerId": 2,
                    "answerMeaningId": "2.01",
                    "createdBy": "Ramadoss E",
                    "createdOn": "2021-09-12T05:27:49.9346289Z",
                    "isDeleted": false,
                    "questionId": 2,
                    "tagId": 2,
                    "tagName": "dev-tag-2"
                },
                {
                    "answerId": 2,
                    "answerMeaningId": "2.01",
                    "createdBy": "Ramadoss E",
                    "createdOn": "2021-09-12T05:27:49.9842107Z",
                    "isDeleted": false,
                    "questionId": 2,
                    "tagId": 3,
                    "tagName": "dev-tag-3"
                },

If you only want to remove the element with tag ID == 1, then of course:

UPDATE formsmanager AS fm 
SET fm.answerVersionsArray = ARRAY v 
                             FOR v IN fm.answerVersionsArray 
                             WHEN IFMISSINGORNULL(NOT (v.questionId = 2 AND v.answerId = 2 AND v.answerMeaningId = "2.01" AND v.tagId = 1) , true)
                             END, 
    fm.createdOn = ""2021-09-13T08:11:00.754Z"
    fm.createdBy = "RamadossE"
WHERE fm.type = "answersCollection"

So I suspect it is just a case of tweaking your filters as necessary.

HTH.

2 Likes