Working with JSON Arrays in N1QL

The N1QL query language has a rich set of facilities for working with  JSON arrays. In this article, we’ll cover array constants, facilities for searching in arrays, ways to transform arrays, and some useful N1QL array functions. The format is interactive; fire up an instance of Couchbase and try the examples yourself.

To begin with, go to the admin console of your Couchbase instance, and create a bucket named “test”.

Alternately, you can create the bucket from the command line, like this:

curl -X POST -u Administrator:password -d name=test -d ramQuotaMB=100 -d authType=sasl -d saslPassword=donotuse -d proxyPort=11224 -d bucketType=couchbase http://localhost:8091/pools/default/buckets

Connect to the instance using the CBQ shell like this:

cbq -e http://localhost:8091 -u Administrator -p password

Then, in CBQ, create a primary index on your new bucket:

cbq> create primary index on test;

Now we’re ready to begin.

Couchbase Array Constants

First of all, N1QL allows you to create array values directly as array constants, like this:

cbq> select [1,2,3] as num_array, ["a", "b", "c"] as string_array, ["1", 2, [3, "4"], {"a":"b"}] as mixed_array;

The constants can even include values pulled from the database:
cbq> insert into test (key, value) values ("ex1", {"v1" : 7, "v2" : "bird", "v3" : 99});

cbq> select [1, v1, 3] as num_array, ["a", v2, "c"] as string_array from test where v3 = 99;

Element selection by zero-based index is also supported:

cbq> select ["apple", "banana", "cherry", "date", "elderberry"][1] as val;

Couchbase Query Arrays and Searching in Arrays

The IN clause is used to test membership.

cbq> select (9 IN [1, 2, 3, 4]) as not_found, (9 in [7, 8, 9]) as is_found;

The ANY clause is used to verify that at least one element of the array matches some condition:

cbq> select (ANY v IN [1, 2, 3, 4, 5] SATISFIES v > 4 END) as is_found, (ANY v IN [1, 2, 3, 4, 5] SATISFIES v = 7 END) as not_found;

The EVERY clause is used to verify that all elements of an array matches some condition:

cbq> select (EVERY v IN [1, 2, 3, 4, 5] SATISFIES v > 0 END) as is_found, (EVERY v in [-1, 0, 1, 2, 3] SATISFIES v > 0 END ) as not_found;

ANY AND EVERY is very similar to EVERY, but EVERY is true for empty arrays, but ANY AND EVERY is false:

cbq> select (ANY AND EVERY v IN [] SATISFIES v > 0 END) as any_every_res, (EVERY v IN [] SATISFIES v > 0 END) as every_res;

Slicing

The [:] operator is for slicing, i.e. taking pieces of arrays.

cbq> select [1, 2, 3, 4, 5][1: 3] as res;

The first value in the slice expression is the index to start at (zero-based); the second is the first index that should be excluded. This convention should be familiar from other programming languages.

Both the start and the end of the slice must be given. Negative values enumerate from the end of the array:

cbq> select [1, 2, 3, 4, 5][1: -1] as res;

Transforming Couchbase Arrays

The ARRAY expression lets you transform an array into another.

cbq> select ARRAY v*2 FOR v IN [1, 2, 3, 4, 5] END as res;

The ARRAY expression also supports selection:

cbq> select ARRAY v*2 FOR v IN [1, 2, 3, 4, 5] WHEN v > 2 END as res;

The target expression can include not just each value, but also the indexes:

cbq> SELECT ARRAY {v:pos} FOR pos:v IN [ "one", "two", "three" ] END;

To learn more about array operations in N1QL, check the documentation here.

Couchbase Array Functions

N1QL has a broad set of functions for operating on JSON arrays.

ARRAY_LENGTH returns the length of an array.

cbq> select ARRAY_LENGTH([1, 2, 3, 4, 5]) as res;

ARRAY_APPEND adds elements to the end of an array.

cbq> select ARRAY_APPEND([1, 2, 3, 4, 5], 9, 10) as res;

ARRAY_CONCAT is similar, but joins two arrays together.

cbq> select ARRAY_CONCAT([1, 2, 3, 4, 5], [7, 8]) as res;

ARRAY_SORT sorts the elements of an array.

cbq> select ARRAY_SORT([2, 1, 3, 5, 4]) as res;

And finally ARRAY_DISTINCT removes duplicates from an array.

cbq> select ARRAY_DISTINCT([1, 2, 3, 3, 4, 5, 4, 5]) as res;

There are many more useful array functions in N1QL, far too many to cover here. You can find the full list here.

Performance

“But what about performance of JSON arrays?” you ask. Fear not, that is covered in another article, here.