CODEWITHSUNDEEP
sqljsondatabasecouchbasesql++
droidmainiac
droidmainiac

Reputation: 328

How to add a key value in couchbase where the value is derived from an already existing key value in the document

I have a json document in couchbase whose structure looks like this

"root": {
          "type": "TEST",
          "parameters": {
            "numbers": [
              "1",
              "2-001",
              "3",
              "2-001",
              "5-002"
           ] 
          }
        }

I need to add a new key value pair and make the document look like this

"root": {
          "type": "TEST",
          "parameters": {
            "numbers": [
              "1",
              "2-001",
              "3",
              "2-001",
              "5-002"
           ],
          "unique": [
              "1",
              "2",  
              "3",
              "5"
          ] 
         }
        }

I should strip off whatever is after -, and select the unique elements present inside of it.

Upvotes: 1

Views: 359

Answers (1)

Matthew Groves
Matthew Groves

Reputation: 26169

If you want to do this with N1QL, you can use an ARRAY transformation combined with ARRAY_DISTINCT. The transformation will be up to you. You could use one of the REGEXP_ functions or something simple like a SPLIT. For example:

select ARRAY_DISTINCT(ARRAY SPLIT(v,"-")[0] FOR v IN d.root.parameters.numbers END) as `unique`, d.root.parameters.numbers
from mybucket d;

That will return document(s) in the form:

[
  {
    "numbers": [
      "1",
      "2-001",
      "3",
      "2-001",
      "5-002"
    ],
    "unique": [
      "1",
      "2",
      "3",
      "5"
    ]
  }
]

If you want to actually make changes to the document(s), you can make that SELECT into an UPDATE:

UPDATE mybucket
SET root.`unique` = ARRAY_DISTINCT(ARRAY SPLIT(v,"-")[0] FOR v IN root.parameters.numbers END)
where root.`unique` is missing;

Upvotes: 3

Related Questions