Removing an element from a nested array

SQL++
1

Given this field within a document:

"outer": [
        {
		  "name": "apple",
          "inner": [
            {
              "foo": 8,
              "bar": 2008
            },
            {
              "foo": 9,
              "bar": 2009
            }
          ]
        }
      ],

I want to remove the element {"foo": 8, "bar": 2008} from "inner".
I can remove an element from "outer" by building the array and filtering out an element like so
UPDATE inventory d USE KEYS "key1" SET d.outer= ARRAY v for v in d.outer WHEN v.name != "apple" END
But I’m having trouble referencing an element within the nested array and filtering for bar.

Any tips or examples would be appreciated.

2

based on condition

UPDATE inventory d USE KEYS "key1" 
SET o.`inner` = ARRAY v FOR v IN o.`inner` WHEN v.foo !=8 OR v.bar != 2008 END
                FOR o IN d.`outer` WHEN o.name = "apple" END

OR

based on value

UPDATE inventory d USE KEYS "key1" 
SET o.`inner` =  ARRAY_REMOVE(o.`inner`,{"foo":8, "bar":2008})
                FOR o IN d.`outer` WHEN o.name == "apple" END
2 Likes
3

@vsr1 Is it possible to run two different statements depending on a conditional? Take my previous example,
suppose if ARRAY_COUNT("inner") > 1, then I can use your statement to just remove one of the element in the “inner” array.

UPDATE inventory d USE KEYS "key1" 
SET o.`inner` = ARRAY v FOR v IN o.`inner` WHEN  v.bar != 2008 END
                FOR o IN d.`outer` WHEN o.name = "apple" END

Else, I want to use this statement to remove the entire element in the “outer” array:

UPDATE inventory d USE KEYS "key1" 
SET d.outer= ARRAY v for v in d.outer WHEN v.name != "apple" END

I know it’s possible to use CASE conditionals for expressions but looks like it’s not possible for statements. Do I need to use subqueries? Once again any pointers appreciated!

4

@ess , Try this

UPDATE inventory d USE KEYS "key1"
SET d.outer = ARRAY (CASE WHEN o.name != "apple"
                          THEN o
                          ELSE OBJECT_PUT(o,"inner",(ARRAY i FOR i IN o.inner WHEN i.bar != 2008 END))
                     END)
              FOR o IN d.outer
              WHEN o.name != "apple" OR (ANY i IN o.inner SATISFIES i.bar != 2008 END)
              END;
1 Like
5

@vsr1 Thanks so much for your help again, but there seems to be some problems with this query. When inner has > 1 element, I just want to filter out the matching element i.e., bar != 2008.
When inner has just 1 element and there is a matching element, then instead of removing the inner element, I want to remove the entire outer element which includes name and inner.

To demonstrate the end results:
Given this field and name = “apple”, bar = 2008

"outer": [
        {
		  "name": "apple",
          "inner": [
            {
              "foo": 8,
              "bar": 2008
            },
            {
              "foo": 9,
              "bar": 2009
            }
          ]
        }
      ],

I want the field post query to look like this.

"outer": [
        {
		  "name": "apple",
          "inner": [
            {
              "foo": 9,
              "bar": 2009
            },
          ]
        }
      ],

Now if we run the query again with name = “apple”, bar = 2008, then nothing should happen.
However if we run the query again with name = “apple”, bar = 2009 then,

"outer": []

Right now with name = “apple”, bar = 2008, the query removes name along with the matching inner element in the first run. If I run the query again with the same vars, it will remove the remaining element in inner which I do not want removed.

6

@ess Updated previous post try again.

7

The query works great now if there is only one element in outer.
Unfortunately if there are multiple elements it will remove all elements with foo = 2008, even if name is different.

For example if given this:

"outer": [
        {
          "name": "apple",
          "inner": [
            {
              "foo": 8,
              "bar": 2008
            },
            {
              "foo": 9,
              "bar": 2009
            }
          ]
        },
       {
          "name": "pears",
          "inner": [
            {
              "foo": 8,
              "bar": 2008
            },
            {
              "foo": 9,
              "bar": 2009
            }
      ],

I don’t want the element name “pears” to be mutated at all.

8

It will work. Please checkout CASE WHEN o.name != “apple” THEN o ELSE

9

Oops, sorry! My mistake. Your query works great. Thanks for using object functions here, didn’t know they could be used this way in conjunction with collection operators. I’m sure it’ll be helpful for me in the future.