Delete a Sub Document

SQL++
1

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\""
  }
]
2

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 .

1 Like
3

thank you it works perfectly :slight_smile:

1 Like
4

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

5

Yes, try it out and tell us.

6

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:

7

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

8

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

9

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

Looks good. I missed your USE KEYS initially.

11

@geraldss @a.u - how can we make sure N1QL is ACID complaint and there will not be a locking during race condition ? as I don’t see N1QL deal with CAS unlike CB Python API bucket operations

thanks

12

N1QL UPDATE full document mutation and check CAS value internally. Returns error if CAS mismatched and user can retry update operation. ACID is document level not statement level.