N1QL to remove array elements

SQL++
1

Hi,
I am using Java SDK 2.2.8 SubDoc API.
I am in need to remove elements from a array and have tried to remove using index. (mutateIn().remove());
Using N1QL can the elements be removed more efficiently? If yes, please provide a sample query.

Appreciate any help.
Thanks

2

Hey,

you may search for this:
https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/arrayfun.html
With “ARRAY_REMOVE(expression, value, …)” you can remove one or more items.

1 Like
3

Thanks Frank.
If my document is nested and the array is present at a different level, how do we mention the path in the update query. I am using the N1QL mentioned here Removing elements in a JsonArray

My document is similar to shown below :

{  
  "root_path":{  
    "level_1":{  
      "level_2":{  
        "contents":[  
          {  
            "field1":"value1",
            "field2":"value2",
            "field3":"value3"
          },
          {  
            "field1":"value1",
            "field2":"value2",
            "field3":"value3"
          }
        ]
      }
    }
  }
}

Please help me out with N1QL to query the contents array on level_2.

4

try this

select root_path.level_1.level_2.contents from default USE KEYS ["YOUR DOC'S KEY"]
1 Like
5

In your case ARRAY elements are objects you need to provide the object you want to remove from ARRAY. All matching elements are removed. Check out https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/arrayfun.html

SELECT ARRAY_REMOVE(root_path.level_1.level_2.contents, { “field1”:“value1”, “field2”:“value2”, “field3”:“value3”}) FROM default WHERE …

If you want remove the element from ARRAY based on condition you can construct new array using array-expression. Checkout https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/collectionops.html

If you need to update document after removing you can also check out UPDATE-FOR At https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/update.html

1 Like
6

Thanks @vsr1 and @atom_yang