CouchbaseLite subquery with N1QL


I am using the package cbl in my Flutter app for Android.
The problem I have is that I want to filter an array in a document which is stored in the couchbaseLite database.

Unfortunately UNNEST is not supported (query-n1ql-mobile-server-diffs) but I think a subquery would do the job.

The query I would like to execute:

SELECT (SELECT VALUE FROM db.materials AS m WHERE LIKE '%test%') as materials
FROM _ AS db
WHERE type = 'materials';

The query works perfectly fine when I execute it on the Couchbase Server but doesnt when I try to execute it in CouchbaseLite.


I/flutter (18175): DatabaseException(N1QL syntax error near character 23, code: invalidQuery)
I/flutter (18175):         SELECT (SELECT VALUE FROM db.materials as m WHERE m.nam...
I/flutter (18175):                       ^

Can you help to figure out if this is a couchbaseLite problem or if it is a problem with the flutter package.

It is not a problem with the flutter package. Unfortunately subqueries are also not supported in mobile N1QL at this point in time.

Ok. Thank you for your reply.

This is my current solution:

SELECT materials
WHERE type = 'materials'
    AND ANY m IN materials SATISFIES LIKE '%test%' END;

which is equivalent to this query which I build with the QueryBuilder:

final query = const QueryBuilder()

This would return every document which contains a material with this name.
But this is not what I need. I only want the materials which I have searched for.

It is not performant if I filter the materials in memory on the phone.

The query I was looking for was this:

SELECT ARRAY m FOR m IN materials WHEN LIKE '%test%' END AS materials
WHERE type = 'materials '
    AND ANY m IN materials SATISFIES LIKE '%test%' END

My data structure looks like this:

"materials": [
      "id": "000000000000000100-02MS-01",
      "manufacturer_number": null,
      "material_number": "100",
      "name": "test",
      "serial_number_mandatory": false,
      "storage_bins": []
      "id": "000000000000000101-02MS-01",
      "manufacturer_number": null,
      "material_number": "101",
      "name": "temp",
      "serial_number_mandatory": false,
      "storage_bins": []

Does anyone have an idea if this is currently possible with couchbaseLite?