N1QL - Delete nested objects

SQL++
1

We are having documents (e.g. the following with Key ‘DC1::200::m::100012004’) where we want to delete / unset the complete object(s) which have a specific value (e.g. “export_utc”: 20170813143424):

{
  "type": "material",
  "gtin": {
    "export_utc": 20170813143425,
    "no": "07613273032451"
  },
  "inventory": {
    "A50B": {
      "export_utc": 20170813143424,
      "bmeng": 0,
      "stock": 901
    },
    "Q001": {
      "export_utc": 20170813143425,
      "bmeng": 0,
      "stock": 87
    },
    "Q003": {
      "export_utc": 20170813143425,
      "bmeng": 0,
      "stock": 87
    }
  },
  "created": 1502457823089
}

I already found OBJECT_REMOVE and accoring to the Couchbase documentation would expect it to remove the object from the document. But it seems not to persist the change, maybe because of the SELECT:

SELECT
   OBJECT_REMOVE(c.inventory, to_remove.name) as new_inv, to_remove, meta(c).id
FROM
   cia c UNNEST (
      ARRAY fields FOR fields IN OBJECT_PAIRS(inventory) 
      WHEN 
          fields.val.export_utc=20170813143424 
      END
   ) AS to_remove
WHERE
   meta(c).id='DC1::200::M::100012004'

Does SELECT OBJECT_REMOVE only remove from the results of the SELECT but not make these changes persistent?

Because we may have different objects (with changing names) in our inventory object, we need a way to delete / unset all inventory objects with an outdated export_utc.

Any suggestions on how to remove those inventory child objetcs from our documents would be highly appreciated.

2

@techilla,

OBJECT_REMOVE() is function can be used with in any statement(SELECT, UPDATE,UPSERT, DELETE, INSERT,MERGE) . If the data need to persistent you need to change that using UPDATE statement.

The condition can result multiple fields (don’t know how many) removal rebuilding object below is right approach. Handled export_utc MISSING and NULL

   UPDATE default d
    SET d.inventory =
          OBJECT ov.name:ov.val  FOR ov IN
                (ARRAY v FOR v IN OBJECT_PAIRS(d.inventory)
                WHEN v.val.export_utc IS MISSING OR
                           v.val.export_utc IS NULL OR 
                           v.val.export_utc != 20170813143424
                END)
          END
    WHERE META(d).id = 'DC1::200::M::100012004' AND
        ANY v IN OBJECT_PAIRS(d.inventory) SATISFIES v.val.export_utc = 20170813143424 END;
1 Like
3

This works. Many thanks.