Replace array within an object within an array with new array

SQL++
,
1

Hello,

I have the object in couch

  {
    "id": "metaId"
    "bucketName": {
      "contexts": [
        {
          "filters": [
            {
              "field": "status",
              "values": [
                "1"
              ]
            },
            {
              "field": "signals",
              "values": [
                "4", "5"
              ]
            },
          ],
          "name": "search"
        }
      ],
      "is_active": false,
      "name": "test",
    }
  },

I want to replace all values array with new one using n1ql.
To do that i need to loop over contexts, finding the correct name (“test”), loop over its filters, finding the correct filter by the right field (lets say “signals”) and update its values.

i thought it should be something like this:

UPDATE bucketName USE KEYS metaId SET
context.filters[j].values = $values
FOR context IN contexts WHEN context.name = $name
FOR j IN RANGE ARRAY_LENGTH(context.filters) WHEN context.filters[j].field = $field END END

Obviously it does not work, someone have any idea how to do that?
Thanks

2

You can try:

UPDATE bucketName USE KEYS ["metaId"]                                                                                                                      
SET contexts = ARRAY {"name":c.name
                     ,"filters":CASE WHEN c.name = $name
                                THEN ARRAY {"field":f.field
                                           ,"values":CASE WHEN f.field = $field
                                                     THEN $values
                                                     ELSE f.`values`
                                                     END
                                           } FOR f IN c.filters END
                                ELSE c.filters
                                END
                     } FOR c IN bucketName.contexts END
;

HTH.

3

Or perhaps better since it only updates the specific fields:

UPDATE bucketName USE KEYS [ "metaId" ]                                                                                                    
SET contexts = ARRAY CASE WHEN c.name = $name
                     THEN object_put(c
                                     ,"filters"
                                     ,ARRAY CASE WHEN f.field = $field
                                            THEN object_put(f,"values",$values)
                                            ELSE f
                                            END
                                      FOR f IN c.filters END
                                    )
                     ELSE c
                     END
               FOR c IN bucketName.contexts END
;

HTH.

4 
UPDATE bucketName AS b USE KEYS ["metaId"]
SET f.`values` = $values
    FOR f IN c.filters 
          FOR c IN b.contexts
   WHEN c.name = $name AND f.field =$field END;

FOR is reverse (compare to most languages) and WHEN is only one that applies inner most (you can add outer loop also). This might be expensive when outer condition is false (but that is way syntax to simplify)

1 Like
5

thank you it works! would like to also to remove specific filter from specific context and add new filter to specific context. can you please also help me with that?

6

Thanks for the response!
since my query is under `` quotes not sure how to write the f.values after the word “SET”.
tried without the quotes and received values syntax error

7

This is presumably down to the language you’re using. The REST API request received by the Query service needs to have the grave accent quotes. It is down to the language as to how you manage to embed them in the (expected) string.

Often languages permit backslash escaping or doubling; so `\`` or ```` each possibly being grave accent quoted strings containing a single grave accent.

(You may also look to see if your language can have the string in other - double or single - quotes, in which case grave accents won’t need to be escaped.)

You can also consider not using reserved words for field names - say use “vals” instead of “values”, then you won’t need the grave accent quoting around the field names. (Ref: https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/reservedwords.html )

8

You can try:

UPDATE bucketName b USE KEYS ["metaId"]
SET c.filters = array_remove(c.filters,f)
FOR f IN c.filters FOR c IN b.contexts
WHEN c.name = $name AND f.field = $field END
;

to remove the filter on $field from the context $name.

Similarly:

UPDATE bucketName b USE KEYS ["metaId"]
SET c.filters = array_append(c.filters,$new_filter)
FOR c IN b.contexts
WHEN c.name = $name END
;

to add $new_filter to context $name.

$new_filter could also be a literal object construction, e.g.

UPDATE bucketName b USE KEYS ["metaId"]
SET c.filters = array_append(c.filters,{"field":$field,"values":$values})
FOR c IN b.contexts
WHEN c.name = $name END
;

to build the object from $field and $values.

1 Like
9

Thanks for the help both worked perfectly

10

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.