Couchbase lite N1QL: how to query a array for a array of values

user_1970257 0
Viewed 2299

I have data items like this:

{
  "mydata": [
    {
      "title": "item 1",
      "languages": [
        "en",
        "fr",
        "it",
        "pl"
      ]
    },
    {
      "title": "item 2",
      "languages": [
        "fr",
        "es",
        "pt"
      ]
    },
    {
      "title": "item 3",
      "languages": [
        "en",
        "it"
      ]
    }
  ]
}

How can I query for items in an array like ["en", "it"]? It should match all data elements that contain either "en" or "it" (or both) in languages?

Thank you very much for your help.

couchbase-lite n1ql swift ios
3 Answers

It you have stored your document in a bucket named test, this N1QL query will retrieve the items within it you are looking for:

select item 
from test unnest mydata item 
where "en" in item.languages OR "it" in item.languages

You'll need at least a primary index on the bucket to get this query to run.

user_15430050
user_19083480

OK, I figured out how to do this. For anybody who is interested:

func getIdsByPropertyArray(_ property:String, values:[String]) -> Set<String> {
    var matchingIds = Set<String>()

    var whereExpr:ExpressionProtocol = ArrayFunction.contains(Expression.property(property), value:Expression.string(values[0]))
    for i in 1..<values.count {
       whereExpr=whereExpr.or(ArrayFunction.contains(Expression.property(property), value: Expression.string(values[i])))
    }

    let query = QueryBuilder
        .select(
            SelectResult.expression(Meta.id)
        )
        .from(DataSource.database(database))
        .where(whereExpr)
    do {
        for result in try query.execute() {
            matchingIds.insert(result.string(forKey: "id")!)
        }
    } catch let error {
        print(error.localizedDescription)
    }
    return matchingIds
}

It works, but: it is extremely slow! It takes about 4 seconds with a dataset of 1.000 documents. Now I get all documents from the database and sort out the ones I need with reduce. This only takes about 0.002 seconds …

user_19702570
Related