How to remove objects from an embedded array based on their properties?

By example, a record has the following field “users” (an embedded array containing objects):
{“status”:“active”, “name”:“Alice”},
{“status”:“active”, “name”:“Bob”},
{“status”:“pending”, “name”:“Clark”},


Using 2.2 SQL syntax, I can remove a specific item from that array by doing this:
UPDATE #1:1 REMOVE users = {“status”:“active”, “name”:“Bob”}

But what is I wanted to remove all of the items with status=pending? Is there a way to do this with 2.2. SQL syntax? What about 3.0 SQL syntax?

Lost in the migration…

Hi @eric24

You can use the following syntax:

UPDATE  Foo SET users = users[status != "pending"]

I hope it helps


Thanks @luigidellaquila! That works great.

And this does too (“opposite” logic):
UPDATE ... REMOVE users = users[status = "pending"]

Is there a similar syntax for doing this if the embedded items are not objects? For example, how would you remove an item by value from:
["Alice", "Bob", "Clark"]
[111, 222, 333]

This does not work:
UPDATE ... REMOVE users = users["Alice"]
UPDATE ... REMOVE users = users[111] (This tries to remove the item at index 111)

PS - I assume that your set suggestion and/or a remove operation on an embedded array field is atomic?

@luigidellaquila - Any thoughts on this?