Querying 2d array elements

Couchbase Server
1

I’m new to Couchbase and my JSON is,
…
{
“name”: “device-name”,
“datapoints”: [
[
1453338376222,
10,
3
],
[
1453338377222,
10,
1
]
],
“attributes”: {
“host”: “server1”,
“customer”: “xyz”
}
},
…

inside the 2D array, i have Timestamp, Value, Quality.
How do i write a query based on Timestamp and/or value.?

Thanks in advance.

2

SELECT * FROM default WHERE name = "device-name" AND ANY v IN datapoints SATISFIES v[0] = 1453338377222 AND v[1] = 10 END;

3

query executed, but not showing any results. Thanks.

4

Checkout your bucket name and data. The following works.

INSERT INTO default VALUES("k01", { "name": "device-name", "datapoints": [ [ 1453338376222, 10, 3 ], [ 1453338377222, 10, 1 ] ], "attributes": { "host": "server1", "customer": "xyz" } });
INSERT INTO default VALUES("k02", { "name": "device-name", "datapoints": [ [ 1453338376222, 10, 3 ], [ 1453338377222, 11, 1 ] ], "attributes": { "host": "server1", "customer": "xyz" } });
CREATE PRIMARY INDEX ON default;
SELECT * FROM default WHERE name = "device-name" AND ANY v IN datapoints SATISFIES v[0] = 1453338377222 AND v[1] = 10 END;
5

Thanks. It works, my mistake. But it returns all datapoint array items, instead of the one matches. And also how query BETWEEN timestamps.

6 
SELECT * FROM default WHERE name = "device-name" AND ANY v IN datapoints SATISFIES v[0] BETWEEN 1453338377222 AND  1453338377300  AND v[1] = 10 END;


SELECT ndatapoints 
FROM default 
LET ndatapoints = ARRAY v FOR v IN datapoints WHEN v[0] BETWEEN 1453338377222 AND  1453338377300  AND v[1] = 10 END
WHERE name = "device-name" AND  ARRAY_LENGTH(ndatapoints) > 0;
7

Thanks for your reply. First query works. Second one giving syntax error code 3000 with “msg”: “syntax error - at WHEN”. what could be the reason.?

8

Updated previous post

9

Thank you, it works.