Create Object on-the-fly during N1QL update

SQL++
1

I am wondering if there is a way to create an object in an existing document during an N1QL update.

For example:

{
  "items": [
    {
      "id": 123,
      "values": [
        {
          "1": [
            "String"
          ],
          "2": [
            "String 2"
          ]
        },
        {
          "1": [
            "String"
          ],
          "2": [
            "String 2"
          ]
        }
      ]
    },
    {
      "id": 456,
      "values": [
        {
          "1": [
            10
          ]
        }
      ]
    },
    {
      "id": 789,
      "values": [
        {
          "1": [
            "String 1",
            "String 2",
            "String 3"
          ],
          "2": [
            "String 1",
            "String 2",
            "String 3"
          ]
        }
      ]
    }
  ]
}

In the document above, there is no “location” object. During my query, Id like to check and see if the object exists before I try to add values into it.

I tried using the CASE statement, but it doesn’t seem to work this way as everything after the SET clause expects key/value representations. I checked the Object functions, but they all require an input object, and there seems to be no way to provide the document being updated into these functions.

So basically the document would look like this after:

{
    "items":{...},
    "location": {...}
}

Given the fluidity of JSON structures, it would be cool to be able to perform this kind of function during an update.

So, do I have any options? Or do I need to do a select/update, first?

Thank you.

2

The closest I came was this:

UPDATE default USE KEYS "k100" 
    SET `location` = CASE WHEN `location` IS MISSING THEN {"hello":"world"} 
    ELSE OBJECT_ADD(`location`, "hello", "world") END
3 
UPDATE default AS d  USE KEYS ["k100"]
       SET d.location = {"loc":"abc"}
       WHERE d.location IS MISSING;
4

This isn’t quite what Im talking about.

So, lets say any part of a sub-document does not exist yet, but I am trying to write a new value to it. Id like to create that object/array during query execution.

My solution above works, but its not possible to use it in a loop, it seems. So it becomes impossible to do it for nested documents. At least to my knowledge.

{
  "key_1": {
    "inner_key_1": [
      {
        "inner_inner_key_1": "value"
      }
    ]
  },
  "key_2": {}
}

Assuming “key_1” and “key_2” are supposed to have identical structures, Id like to create “inner_inner_key_2” in one update query, first checking to see if it exists. If it doesn’t, create it, if it does, update it.

I’ve been playing around with different mixes of CASE and SET, but can’t seem to get the engine to do it. It may not be possible, but I want to make sure I ask it here, first.

5

It is possible. The following example demonstrates how to add the element if not exit and update sub element when is present.

Run first time UPDATE check document and run second time UPDATE and check again.

 INSERT INTO default VALUES("k100",{ "items": [ { "id": 123, "values": [ { "1": [ "String" ], "2": [ "String 2" ] }, { "1": [ "String" ], "2": [ "String 2" ] } ] }, { "id": 456, "values": [ { "1": [ 10 ] } ] }, { "id": 789, "values": [ { "1": [ "String 1", "String 2", "String 3" ], "2": [ "String 1", "String 2", "String 3" ] } ] } ] });
 UPDATE default AS d  USE KEYS ["k100"]
            SET d.location = {"loc":{"name":"abc1","k1":0}} FOR v IN CASE WHEN d.location IS MISSING THEN [d.location] END WHEN v IS MISSING END,
                d.location.loc.k1 = 5 FOR v IN TOARRAY(d.location) WHEN v IS NOT MISSING END;
1 Like
6

Great! So it seems the only solution here would be multiple updates on the same document. Thanks for the answer!