as I hoped. But I am having trouble finding out how to specify an entire element to be removed.
It seemed reasonable that the following would be how it was done:
UPDATE default USE KEYS “theDatabase”
UNSET item FOR item IN theArray WHEN item.field1=“this” END
but that leaves the document unchanged.
The closest I can get is by specifying each of the fields in the element, specifically, but that simply leaves an empty element. While what I am trying to do is remove the element entirely. Is there a way this would be done?
I find it embarrassing to find a solution for which the necessary information was already in the documentation out there, but that solves it perfectly. Changed slightly, this produced the result I was aiming for:
UPDATE default USE KEYS “theDatabase”
SET theArray = ARRAY item FOR item IN theArray WHEN item.field1 != “this” END
No embarrassment! We are trying to provide the most natural syntax for all these scenarios, and it is very helpful for us to see real questions from users.
That sounds great, but I was wondering if I could use the ARRAY_REMOVE function instead, but I can’t find any examples of what to use for expression and value params
I see. We don’t have that. We have stayed with a more SQL-oriented syntax. But we will keep that option in mind. We get the question from time to time. Thanks.
Hi…
I have a json document with below data -
{
“updateDescriptions”: [
{
“id”: “10001”,
“library”: “For Profit”,
“submittedBy”: “test user1”,
“submittedDate”: “10/12/2017”
},
{
“id”: “20002”,
“library”: “For Profit”,
“submittedBy”: “test user1”,
“submittedDate”: “10/12/2017”
}
]
}
I want to delete where id=“20002”. I have used this query , but not giving the expected result. UPDATE Library UNSET x FOR x IN updateDescriptions WHEN x.id = “20002” END where meta().id like “sample:updateDescription” ;
UPDATE Library
SET updateDescriptions = ARRAY v FOR v IN updateDescriptions WHEN v.id != "20002" END
WHERE META().id LIKE "sample:updateDescription%" AND
ANY v IN updateDescriptions SATISFIES v.id = "20002" END;
I need to update the AutogrntList array and remove all the elements where "EndDt" is greater than "2021-09-24", I need to delete the below element from the array AutogrntList
{
"CustListNum": 55555,
"PrmtnGrpdEvntDocId": "PromotionGroupedEvent::99999_32_301070_157",
"EffDtRnge": {
"EndDt": "2021-09-25",
"StrtDt": "2021-06-27"
}
I am using the below query to get the list of documents from the object
SELECT t.Id,A.*
FROM `masterdata-target` t USE KEYS[ 'CustomerAutograntList::88888_157']
UNNEST AutogrntList A
WHERE t.`$Type`='CustomerAutograntList'
AND ( A.EffDtRnge.EndDt >=NOW_STR()
AND A.EffDtRnge.StrtDt<=DATE_ADD_STR(NOW_STR(), 14, 'day'))
But i am not sure how to update the document. Could you please share the update query.
UPDATE `masterdata-target` t USE KEYS[ 'CustomerAutograntList::88888_157']
SET t.AutogrntList = ARRAY v FOR v IN t.AutogrntList WHEN v.EndDt <= "2021-09-24" END
WHERE t.`$Type`='CustomerAutograntList'
AND ( A.EffDtRnge.EndDt >=NOW_STR() AND A.EffDtRnge.StrtDt<=DATE_ADD_STR(NOW_STR(), 14, 'day'))
AND ANY v IN t.AutogrntList SATISFIES v.EndDt > "2021-09-24" END
Reconstruct array with required elements and SET
`SET t.AutogrntList = ARRAY v FOR v IN t.AutogrntList WHEN v.EndDt <= "2021-09-24" END`
Only mutate document only if need to remove element
` AND ANY v IN t.AutogrntList SATISFIES v.EndDt > "2021-09-24" END`
this is one object in the document which I want to update and remove the value where is key= “5” and value= 2, so at the end, I want to get all documents without “5”:2 value in abTests object