Ios swift queries with arrays containing objects

n1ql

#1

object is as follows:
{
“labels” : {
“values” : [
{"$oid": “1234”},
{"$oid": “1235”},
{"$oid": “1236”},
{"$oid": “1237”}
]
}

can’t get an Expression (and Join) to be in a where clause to match a specific id (N1QL seems to have many primitives to do that, but these are not available currently in 2.1.1 ios swift bindings.

Any suggestion?


#2

Please be more specific about what you are asking. Mobile does not have the equivalent to everything in N1QL so the feature you are after may not be available but it is unclear, at least to me, what you are after.


#3

in the example above, “values” is an array of {"$oid": “1234”}, {"$oid": “1235”}. If I want to do a where Expression.string(“1235”) is present in this array, I can’t find how to do it with 2.1.1 swift ios release. The in does not seem to go inside the array. It appears that I would need some kind of aggregate/nesting functions available in normal N1QL queries, but not yet exposed/available in the swift ios bindings. Clearer?


#4

Check out this blog which discusses examples of querying array elements. There is one similar to the case you have. Here is the corresponding playground. Specifically look into the Array expression.


#5

Can't get it right. Would it be possible that the $ in the ArrayExpression.variable("value.$oid") statement would cause a problem in the underlying SQLite? the $ seems to have a special meaning for SQLite as I can see. Could it be passed through and SQLite does not like it?


#6

If it doesn’t with SQLite, the Couchbase Lite actually gives a special meaning to this character so it would be best to avoid it if possible. I raised the issue internally and a fix is being developed (or has been, I can’t remember) so hopefully it won’t be an issue in the future.


#7

up to now, the dollar sign worked in Expression.property(). The source of the JSON is from MongoDB who uses the { $oid: “hexid” } form for any id in various nested places. Therefore, it is very complicated to avoid it. Should I fill a bug somewhere?


#8

If it works in property then I’d expect it to work elsewhere too (looks like that fix already went in). Perhaps post the join you are attempting to use.


#9

just in a where statement (no join):
.and(ArrayExpression.any(ArrayExpression.variable(“a”)).in(Expression.property(“b.c”))
.satisfies(ArrayExpression.variable(“a.$oid”).equalTo(Expression.string(“191230923acd12”)))))

my take is that ArrayExpression.variable is the issue, while Expression.property is ok with “d.$oid”.


#10

Could you also post an example of the document you are running this on? Can I replace “b.c” with “labels.values” in the above example?


#11
{
     "labels" : {
        "values" : [ 
            "$oid": "56c20025533f4c14e4000799",
            "$oid": "56c20027533f4c14e40007af"
       ]
     }
}

#12

yes you can replace as proposed.


#13

Ok I filed this ticket based on this information.


#14

the fix seems to make where clauses ok, but I’m not getting results for joins. Might be my join statement or another code path not fixed. docs:

{
   "type: "product",
     "labels" : {
        "values" : [ 
            "$oid": "56c20025533f4c14e4000799",
            "$oid": "56c20027533f4c14e40007af"
       ]
     }
}
{
 "_id": {
       "$oid": "56c20025533f4c14e4000799"
    },
  "type": "label"
}

(this is implemented as Extension Database, therefore self used in database(self))
query:

let labelsDS = DataSource.database( self ).as("l")
let thisDS = DataSource.database( self ).as("p")
let q = QueryBuilder.selectDistinct(SelectResult.all().from("l"))
.from(labelsDS)
.join(Join.join(thisDS)
   .on(ArrayExpression.any(ArrayExpression.variable("d"))
       .in(Expression.property("labels.values").from("p"))
.satisfies(ArrayExpression.variable("d.$oid")).equalTo(Expression.property("_id.$oid").from("l"))                        .and(Expression.property("type").from("l").equalTo(Expression.string("label")))
.and(Expression.property("type").from("p").equalTo(Expression.string("product")))))

should return the object of “_id.$oid” = “56c20025533f4c14e4000799”


#15

Have you tried using a different property name that doesn’t start with a $, and does that make the problem go away?

If so, that indicates some other problem with $

In either case, please call print(q.explain()) and post the results. I’m interested in seeing the internal JSON form of the query. (I work on the query implementation and the JSON form is a lot more readable to me than the public API!)


#16

ok. I’m pretty sure I found the issue. There seems to be another front character that causes issues: “_”. On a ArrayExpression.variable(“var._id.$oid”) statement in a join statement, query does not work. If I change the data to not have the “_” and then do a ArrayExpression.variable(“var.id.$oid”) statement, the query works. no other change. (obviously $oid works because of previous fix applied)


#17

You cannot use _id or any keys with an underscore. Those kinds of keys are reserved for Couchbase Lite use. In fact _id is going to attempt to use the actual metadata ID.

EDIT I should say any keys beginning with an underscore.


#18

that is going to be a big problem. any workaround? Can I escape it by some means?


#19

I don’t know if you saw my edit, but it only affects properties beginning with an underscore. Technically I suppose the story is less cohesive than that and I don’t know if it’s changed since I last checked, but you should be able to write these keys, you just won’t be able to query some of them since they have predefined meaning for the query engine. This is the reason that we say they are reserved.

To confirm the behavior, check the output of selecting the _id property and see if it is the document’s ID. If not then what I said was wrong.

EDIT As far as a workaround, you can add any character besides the underscore to the beginning to avoid the problem


#20

I was talking about a workaround without modifying the data, i.e. in the query. Tried adding a \ without success. I won’t be able to change the data, as this is coming from a mongodb source which uses that paradigm all over.