UNNEST Query Not Working in Java with Couchbase Lite, But Works in Couchbase Capella

Mobile Couchbase Lite
, , ,
1

I’m having trouble with an UNNEST query in my Java application using Couchbase Lite. The same query works perfectly on the Couchbase Capella server, but I’m encountering issues when executing it from my Java application.

Objective: I need to count the number of items in a list based on a specific key value from another key in the JSON structure. The structure of the JSON and the sample query are provided below.

[
{
“key1”: {
“key2”: “value1”,
“key3”: “value2”,
“key4”: [
{
“key5”: “value3”,
“key6”: “value4”,
“key7”: “value5”,
“key8”: “value6”,
“key9”: false,
“key10”: “value7”,
“key11”: “value8”,
“key12”: “value9”
},
{
“key5”: “value10”,
“key6”: “value11”,
“key7”: “value12”,
“key8”: “value13”,
“key9”: false,
“key10”: “value14”,
“key11”: “value15”,
“key12”: “value16”
}
],
“key13”: “value17”
}
}
]
Sample Query: String stringQuery = "SELECT COUNT(*) AS unreadCount "
+ "FROM collection AS doc "
+ "UNNEST doc.key4 AS list "
+ "WHERE doc.key3 = ‘value2’ "
+ “AND list.key9 = false;”;
Query query = QueryBuilder.createQuery(stringQuery, database);
ResultSet resultSet = query.execute();

Issue:

  • The query works correctly when run directly on the Couchbase Capella server.
  • When executing the query from my Java application, it fails.

Has anyone encountered a similar issue or have suggestions on how to properly execute UNNEST queries in Couchbase Lite using Java? Any advice or guidance would be greatly appreciated!

Thank you!

2

In general, you will have a much better chance of getting help with issues like this if you provide some information about the environment (hardware, os, etc) in which you are experiencing a problem, and if you actually demonstrate the problem (include an error message; something like that)

In this particular case, it turns out, you are lucky: none of that is necessary. Couchbase Lite does not support UNNEST. We are working on support for it, at this moment and will support it in an upcoming release.

2 Likes
5

Thank you for the clarification!

It’s good to know that it’s something you’re actively working on. We will adjust our approach accordingly and look forward to the upcoming release with UNNEST support.

Thanks again for the quick response!