Array insert at index N1QL

SQL++
1

Hi, is there a N1QL statement that allows me to insert an element to a specific index of an array?

Thank you

2

Hi, There are several options:

UPDATE b SET a[i] = 5 /* update in place /
UPDATE b SET a = ARRAY_APPEND(a, 5) / append /
UPDATE b SET a = ARRAY_PREPEND(5, a) / prepend /
UPDATE b SET a = ARRAY_CONCAT(a[0:i], ARRAY_PREPEND(5, a[i+1:]) / insert at i */

It looks like we need ARRAY_INSERT(). We’ll add this.

Thanks,
Gerald

1 Like
3

Thanks a lot for the answer!

Luigi Donadel

1 Like
4

Hi, we are using the array_append, but the problem is that when our array gets bigger the update is obviously slow, so we are wondering if there is a better way to perform that type of operations, we also try to get the document on the app side and append de item to the array and perform an upsert but it was also to slow depending on the size of the array.

We hope some one could help us; it is not an option to break the array in single documents at the time.

Thanks

5

How big is the array, how many elements?

6

Hi, we detect the problem start to appear when we have arrays with more than 1.5k elements, each element is an object with 6 properties (3 strings), (2 Integers), (1 datetime)

7

Ok, that’s a lot of elements. How big do you expect the arrays to get? How far will they grow beyond the 1.5K elements?

Also, please post your query here and we will take a look and offer any suggestions.

8

It depends entirely on the performance of couchbase, if we can reduce the latency on some way with that 1.5k we will be happy.

Our query is something like this:

Couchbase.N1QL.QueryRequest.Create(string.Format(“update {0} USE KEYS “{1}” set messages = ARRAY_APPEND(messages, $message) where _type = $type”, bucket.Name, eventKey));
var json = JsonConvert.SerializeObject(newMessage, new JsonSerializerSettings { TypeNameHandling = TypeNameHandling.Objects });
request.AddNamedParameter("$message", JToken.Parse(json));
request.AddNamedParameter("$type", typeof(Event).Name);
var result = bucket.Query(request);

9

What version of Couchbase Server are you using?

10

We are currently using 4.1 Community Version

11

@anibal if you know the document ID, you could think about upgrading to 4.5 to make use of our new subdoc API that lets you manipulate parts of the JSON directly via KV which is the most efficient way to handle the situation right now.

1 Like
12

Ok, thank you, we will keep that in mind, when we decide to go in production, we are evaluating right now, but it is good there is a better way to do things like that. Thanks again