Delete a Sub Document

Hey,

my document look like this:

Id couchBase of document is Store::30.

{
  "storeType": {
    "name": "VOD"
  },
  "name": "SFRStore",
  "description": "test description blablabla",
  "categories": [
    {
      "name": "Series",
      "displayable": false,
      "active": false,
      "highlights": [
        {
          "plannings": [
            {
              "planningId" : "p1",
              "endDate": 1479168000,
              "startDate": 1456790400
            },
            {
              "planningId" : "p1",
              "endDate": 1489708800,
              "startDate": 1483228800
            }
          ],
          "name": "Spiderman",
          "active": true,
          "highlightId": "Highlight::21",
          "actionName": "Accèder au contenu",
          "order": 0
        },
        {
          "name": "Spiderman2",
          "active": true,
          "highlightId": "Highlight::22",
          "actionName": "Accèder au contenu",
          "order": 0
        }
      ],
      "categoryId": "CATEGORIESERIES"
    }
  ],
  "storeId": "STORESFR",
  "order": 1
}

How can i delete only:

{
   "planningId" : "p1",
    "endDate": 1479168000,
    "startDate": 1456790400
}

I tried with this query:

DELETE FROM defaut x USE KEYS "Store:30" UNNEST x.categories c UNNEST c.highlights h UNNEST h.plannings p WHERE c.categoryId="CATEGORIESERIES" AND h.highlightId="Highlight::21" AND p.planningId="p1"

Query Result:

[
  {
    "code": 3000,
    "msg": "syntax error - at UNNEST",
    "query_from_user": "DELETE FROM defaut x USE KEYS \"Store:30\"  UNNEST x.categories c UNNEST c.highlights h UNNEST h.plannings p where c.categoryId=\"CATEGORIESERIES\" AND h.highlightId=\"Highlight::21\" AND p.planningId=\"p1\""
  }
]

Hi,

This would be an UPDATE.

UPDATE default x
USE KEYS "Store::30"
SET h.plannings = ( ARRAY p FOR p IN h.plannings WHEN p.planningId <> "p1" END ) FOR h WITHIN categories WHEN h.plannings IS NOT NULL END;

Another pattern @keshav_m @prasad .

thank you it works perfectly :slight_smile:

1 Like

Can i do the same upade using storeId, categoryId, highlightId, and planningId ?

StoreId: Store::1
        {  
           "_class":"Store",
           "categories":[  {  
                 "active":false,
                 "categoryId":"Category::3",
                 "displayable":false,
                 "highlights":[  
                    {  
                       "actionName":"Accèder au contenu",
                       "active":true,
                       "highlightId":"Highlight::3",
                       "name":"Spiderman2",
                       "order":0,
                       "plannings":[  
                          {  
                             "cbPlanningId":"Planning::2",
                             "endDate":-122576256,
                             "startDate":373725440
                          },
                          {  
                             "cbPlanningId":"Planning::1",
                             "endDate":-122576256,
                             "startDate":373725440
                          }
                       ]
                    }
                 ],
                 "name":"Sport"
              }
           ],
           "order":0
        }

I this exemple I want to delete The planning how have the id: Planning::1 and HighlightId: Highlight::3 and categoryId: Category::3 and StoreId Store::1

Yes, try it out and tell us.

I followed the same logic in your query:

UPDATE default g USE KEYS "Store::1"
SET h.plannings = (ARRAY item for item IN h.plannings WHEN item.cbPlanningId <>  "Planning::2" END) 
WHERE g.categories = (ARRAY c for c IN g.categories WHEN c.categoryId =  "Category::3" END) AND h.highlightId = "Highlight::3"

they is no fatal error but the query did not work :frowning:

The problem might be in your WHERE clause. Can you test the SET using a specific document key.

What you mean by: SET using a specific doc key, because i use USE KEYS “Store::1”

The solution is:

UPDATE default g USE KEYS "Store::1"
SET h.plannings = (ARRAY item for item IN h.plannings WHEN item.cbPlanningId <>  "Planning::2" END) 
FOR h WITHIN (ARRAY it for it IN g.categories WHEN it.categoryId = "Category::3" END)  when h.highlightId = "h1" END;

Looks good. I missed your USE KEYS initially.