Update document with specific field - n1ql query

[hamzamaslouh]

Here is a sample document I am trying to update:

{
  "id": "123",
  "subIds": [
    {
      "subId": "562"
    }
  ],
  "instructions": [
    {
      "payments": [
        {
          "transactions": [
            {
             "id": 1,
              "flag": false
            },
            {
             "id": 2,
              "flag": false
            }
          ]
        }
      ]
    }
  ]
}

currently I modify the flag field with the following query :

UPDATE order AS o
USE KEYS ["123"]
SET tr.flag = true
FOR tr IN ARRAY_FLATTEN(ARRAY_FLATTEN(instructions[*].payments,1)[*].transactions,1)
WHEN tr.id IN [1, 2] END;

Currently I search for documents to be modified only by the id field, I would like to add the possibility of also searching by subIds[*].subId in a list of values ​​(for example ["562"])

Can you help me please ?

[dh]

You could try:

UPDATE order o
SET tr.flag = false
  FOR tr IN p.transactions
    FOR p IN i.payments
      FOR i IN o.instructions
  WHEN tr.id in [1,2]
  END
WHERE id = "123"
AND ANY s IN subIds SATISFIES s.subId IN ["562","563"] END

I’ve included the filter as id=“123” rather than USE KEYS, assuming you’ll create the necessary index (since you’re looking to filter on multiple attributes and not simply update a single document). You could of course USE KEYS instead (with the additional filter).
HTH.