Removing elements in a JsonArray

SQL++
1

I am trying to find a way of conditionally removing elements from a JsonArray using UPDATE with UNSET.
I’m wondering if my approach may be off here.

As an example, I have a document called “theDatabase”:

{
“id”: “1256”,
“theArray”: [
{
“field1”: “this”,
“field2”: “that”,
“field3”: “the”,
“field4”: “other”
},
{
“field1”: “tom”,
“field2”: “dick”,
“field3”: “harry”,
“field4”: “someoneelse”
}
]
}

I can delete individual fields in each element, so the following:

UPDATE default USE KEYS “theDatabase”
UNSET item.field1 FOR item IN theArray WHEN item.field1=“this” END

returns:

{
“id”: “1256”,
“theArray”: [
{
“field2”: “that”,
“field3”: “the”,
“field4”: “other”
},
{
“field1”: “tom”,
“field2”: “dick”,
“field3”: “harry”,
“field4”: “someoneelse”
}
]
}

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?

2

UPDATE default USE KEYS "theDatabase"
SET theArray = ARRAY item FOR item IN theArray WHEN item.field1 IS MISSING END

3

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

Thanks much!

4

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.

Ask away, and thank you much.

-Gerald

5

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

6

Hi @luke.sydenham,

expression is the array, value is the value you want to remove.

You can just experiment on the cbq shell.

SELECT ARRAY_REMOVE( [ 1, 2, 3 ], 2 );

7

Thanks,

I was thinking more along the lines of object elements of the array and predicates to match them…

{
“socks”: [{“colour”:”red”},{“colour”:”blue”}, {“colour”:”red”}]
}

Array_remove(socks, “colour=red”)

Returns

{
“socks”: [{“colour”:”blue”}]
}

8

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.

9

That’s cool, i can just use the syntax as you’ve described to the op but just thought that i might be able to do something with array_remove too.

Thanks for your help

1 Like
10

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” ;

Could you please help me with the query.

11 
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;
12

Thanks for your help…it works…

13 
{
  "$TxId": "infa_CustomerAutograntList_550151.70069594.24",
  "$InfRunId": "infa_CustomerAutograntList_550151.70069594",
  "CustDocId": "Customer::888888_157",
  "$Channels": [
    "Promotion.District.66666"
  ],
  "AutogrntList": [
    {
      "CustListNum": 55555,
      "PrmtnGrpdEvntDocId": "PromotionGroupedEvent::99999_25_317532_157",
      "EffDtRnge": {
        "EndDt": "2021-09-25",
        "StrtDt": "2021-06-27"
      }
    },
    {
      "CustListNum": 55555,
      "PrmtnGrpdEvntDocId": "PromotionGroupedEvent::99999_32_293165_157",
      "EffDtRnge": {
        "EndDt": "2021-09-22",
        "StrtDt": "2021-06-27"
      }
    },
    {
      "CustListNum": 55555,
      "PrmtnGrpdEvntDocId": "PromotionGroupedEvent::99999_32_301070_157",
      "EffDtRnge": {
        "EndDt": "2021-09-25",
        "StrtDt": "2021-06-27"
      }
    },
    {
      "CustListNum": 55555,
      "PrmtnGrpdEvntDocId": "PromotionGroupedEvent::99999_32_307914_157",
      "EffDtRnge": {
        "EndDt": "2021-12-25",
        "StrtDt": "2021-12-19"
      }
    },]


	  "$MdfdById": "DBA",
  "CtryCd": "157",
  "GtmuCustId": "1400759",
  "$MdfdTmstmp": "2021-08-31T05:16:04.460Z",
  "Id": "CustomerAutograntList::1400759_157",
  "$DocVrsn": "1.0",
  "$Type": "CustomerAutograntList",

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.
14 
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`
15

Hello,

Could you please help, I need to update all documents and remove specific key values.

“abTests”: {
“5”: 2,
“6”: 1,
“7”: 1,
“9”: 1,
“15”: 2,
“16”: 1,
“17”: 1
},

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

Thank you very much.

16 
UPDATE bucket AS b UNSET b.abTests.`5` 
WHERE  b.abTests.`5` = 2
1 Like
17

thank you very much.