CouchbaseLite subquery with N1QL

Mobile Couchbase Lite
, ,
1

Hi,

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.

Exception:

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.

2

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

3

Ok. Thank you for your reply.

This is my current solution:

SELECT materials
FROM _
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()
        .select(SelectResult.expression(Expression.property('materials')))
        .from(db)
        .where(Expression.property('type')
            .equalTo(Expression.string('materials'))
            .and(ArrayExpression.any(ArrayExpression.variable('material'))
                .in_(Expression.property('materials'))
                .satisfies(ArrayExpression.variable('material.name')
                    .like(Expression.string('%$searchTerm%')))));

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
FROM _
WHERE type = 'materials '
    AND ANY m IN materials SATISFIES m.name LIKE '%test%' END
ORDER BY material.name;

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?