Removing from array inside array

SQL++
1

hi,

i have a document with the following structure:

`{
	"accountId": "4578fce6-1fa9-4d87-892e-edfdca677837",
	"created": "2016-02-24T10:54:49.427+02:00",
	"versions": [{
		"name": "0.0.1",
		"lastUpdate": "2015-12-04T18:04:49.146+02:00",
		"contributors": [{
			"name": "john doe",
			"email": "jd@example.com"
		}]
	}, {
		"name": "0.1.2",
		"lastUpdate": "2016-02-24T10:54:49.427+02:00",
		"contributors": [{
			"name": "dan smith",
			"email": "ds@example.com"
		}, {
			"name": "ann willis",
			"email": "aw@example.com"
		}]
	}]
}`

i need to remove elements from “contributors” array by email, something like

UPDATE bucket1 USE KEYS “key1234” UNSET e FOR e IN versions.contributors WHEN e.email="aw@example.com" END;

this of course doesn’t work. i tried to play with UNNEST in attempt to access the array inside array, but with no luck.

any thoughts on how this can be done, if at all?

thanks!

2

Hi, you need to construct the new array instead:

UPDATE bucket1 USE KEYS "key1234"
SET versions = ARRAY ( ARRAY c FOR c IN v.contributors WHEN c.email <> "aw@example.com" END ) FOR v IN versions END;
3

hi geraldss,

thanks for your prompt response.
unfortunately your query breaks the document format. this is the output i get:

{
        "accountId": "4578fce6-1fa9-4d87-892e-edfdca677837",
        "created": "2016-02-24T10:54:49.427+02:00",
        "versions": [
            [
                {
                    "email": "jd@example.com",
                    "name": "john doe"
                }
            ],
            [
                {
                    "email": "ds@example.com",
                    "name": "dan smith"
                }
            ]
        ]
}

as you see, contributors array replaces the entire versions array now, so that there are no “contributoes”, “name” etc elements anymore. i tried to play with this but i’m still missing something so i failed to make it work.

thanks again

4

We are getting close.

UPDATE bucket1 USE KEYS "key1234"
SET versions = ARRAY {"name":v.name, "lastUpdate":v.lastUpdate, "contributors": ARRAY c FOR c IN v.contributors WHEN c.email != "aw@example.com" END } FOR v IN versions END;