How to update nested json using sql++ query

SQL++
,
1

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 name string to names 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;

Ref : How to write sql++ query for updating nested json array in Couchbase? - Stack Overflow

2

Your statement works - so can be used. Or are you having some issues with it?

You could also write it as:

UPDATE default d
SET v.prop.names = [v.prop.name] FOR v IN d.attribs WHEN v.prop.name IS NOT MISSING END
UNSET v.prop.name FOR v IN d.attribs END
WHERE ANY v IN attribs SATISFIES v.prop.name IS NOT MISSING END;

(Or you could use OBJECT_RENAME() and update the renamed field, etc.)

HTH.

3

what is d in d.attribs?

4

The alias I used for the keyspace on the first line.
(Not strictly necessary, of course.)

5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.