N1QL Nested Array Query

.NET SDK
, ,
1

Hi all,

Versions:
.Net CouchbaseClient 3.1.1
Couchbase Server 6.0.3

We’re having trouble using Named Parameters in N1QL queries for queries regarding nested arrays.

This is an example of a query we can use that works in the Couchbase Query editor:

This is an example of the document we are querying:

{
  "id": 1234,
  "supplierKey": [
    {
      "supplier": "Wholesome Fruit Company",
      "supplierKey": "WFC"
    },
    {
      "supplier": "Oranges 'R' Us",
      "supplierKey": "ORU"
    }
  ]
}

Example Query:

select id
from `Documents`
where any sk in supplierKeys satisfies [sk.supplier, sk.supplierKey] in 
  [['Wholesome Fruit Company', 'WFC'], ['Something else', 'SME']] end

Index:

CREATE INDEX ix_supplierKeys ON Documents((distinct (array [(sk.supplier), (sk.supplierKey)] for sk in supplierKeys end))) WITH { “defer_build”:true }

The query we want to run will allow us to get the ids of any documents that satisfy the condition that the supplierKey contains at least one specified combination of supplier/supplierKey.

Currently, we can run N1QL queries successfully by constructing the various parameters via string interpolation like so:

var queryResult = await _cluster.QueryAsync<SupplierMapping>($@"
select id, supplierKeys
from `Documents`
where any sk in supplierKeys satisfies [sk.supplier, sk.supplierKey] in {str} end
");

Parameter:

str = "[['Wholesome Fruit Company', 'WFC'], ['Something else', 'SME']]"

But similar to SQL, we would like to use named parameters instead of string interpolation (As a best practice, To avoid N1QL injection if that’s possible):

var queryResult = await _cluster.QueryAsync<MasterIdsSupplierMapping>(@"
select id
from `Documents`
where any sk in supplierKeys satisfies [sk.supplier, sk.supplierKey] in $couchbaseSupplierKeys end
", options => options.Parameter("$couchbaseSupplierKeys", couchbaseSupplierKeys));

Parameter:

couchbaseSupplierKeys = new List<SupplierKeyPair>
{
  new SupplierKeyPair
  {
    Supplier = "Wholesome Fruit Company",
    SupplierKey = "WFC"
  },
  new SupplierKeyPair
  {
    Supplier = "Something else",
    SupplierKey = "SME"
  }
};

public class SupplierKeyPair
{
    public string Supplier { get; set; }
    public string SupplierKey { get; set; }
}

However this throws an InternalCouchbaseError when executed.
Can parameters be used in this way, if not is there a way to use parameters in the way we are attempting?

Otherwise, is it ‘safe’ to use string interpolation to construct our N1QL queries?

Thanks!

2

SupplierKeyPair might generating object, it must be List (i.e. ARRAY)

This assumption is not right. unless both keys are there , you need complex query with supplier, when supplier missing this makes even complex .

FTS index has the flexibility query any combination/multiple arrays. MongoDB in Operator Performance: Querying Multiple Arrays. Without N1QL you can use FTS index directly using client APIs

3

Hi vsr1,

The SupplierKey will never appear without the Supplier so this shouldn’t be an issue.

It sounds like we can’t use named parameters for this unfortunately, so we’ll keep using string interpolation to generate our query.

Thanks!
Mr R-DogPlenty

4

You can use named parameters. From Supplier, SupplierKey construct JSON ARAY and pass it