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 m.name FROM db.materials AS m WHERE m.name 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 m.name 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:
WHERE type = 'materials'
AND ANY m IN materials SATISFIES m.name 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 m.name LIKE '%test%' END AS materials
WHERE type = 'materials '
AND ANY m IN materials SATISFIES m.name LIKE '%test%' END
ORDER BY material.name;
My data structure looks like this:
Does anyone have an idea if this is currently possible with couchbaseLite?