sqlcouchbase

How to write sql++ query for updating nested json array in Couchbase?


I have below kind of Json document stored in CB collection

{
  "cti": "B1",
  "v": 3,
  "es": {
    "lb": false,
    "mu": true,
    "t1": true
  },
  "attribs": [
    {
      "desc": "Heading",
      "id": "head",
      "name": "HEAD",
      "prop": {
        "name": "t1",
        "type": "str"
      },
      "vals": [
        {
          "em": "M",
          "isM": true
        }
      ]
    },
    {
      "desc": "Body",
      "id": "body",
      "name": "Body",
      "prop": {
        "type": "str"
      }
    }
  ]
}

I have written below query to update KEY FROM “attribs.prop.name” TO “attrib.prop.names” and VALUE from “t1” to [“t1”], as i am doing schema change and i need to do existing data correction, to make from "name" type string to "names" type array_of_string

UPDATE `your_bucket`
SET attribs = ARRAY
  CASE
    WHEN v.prop.name IS NOT MISSING THEN
      OBJECT_PUT(v, 'prop', OBJECT_PUT(OBJECT_REMOVE(v.prop, 'name'), 'names', [v.prop.name]))
    ELSE v
  END
  FOR v IN attribs END
WHERE ANY v IN attribs SATISFIES v.prop.name IS NOT MISSING END;

Solution

  • I ran your original query you provided in your question on an example document that matches the original document example you provided in the question, and it did as you are trying to do. It converted the attribute to names and the value to an array.