How do I query Arrays in Usergrid collections?

Question

I'm successfully able to GET data from

 GET /mycollection?ql=select data.visitor.badges where data.visitor._id = 'f33498'

Which returns

{
"action": "get",
"application": "313hhlkhj77080",
"params": {
    "ql": [
        "select data.visitor.badges where data.visitor._id = 'f33498'"
    ]
},
"path": "/mycollection",
"uri": "http://xxxx/appservices/xxxxxx/mycollection",
"list": [
    [
        [
            "New Visitor",
            "Cart Abandoner"
        ]
    ],
    [
        [
            "New Visitor",
            "Repeat Visitors",
            "Cart Abandoner"
        ]
    ],
    [
        [
            "New Visitor",
            "Repeat Visitors",
            "Browse Abandoner"
        ]
    ]
],
"timestamp": 1407968065207,
"duration": 35,
"organization": "visitor-baas",
"applicationName": "sandbox",
"count": 3

}

However, I cannot figure out how to modify the following query to allow me to narrow the result set to only those containing a "Cart Abandoner" value in the data.user.badges array.

Is this possible? I've tried:

GET /mycollection?ql=select data.visitor.badges where data.visitor.badges = 'Cart Abandoner'

This appears to return data.visitor.badges arrays where 'Cart Abandoner' is the last position of the array.

GET /mycollection?ql=select data.visitor.badges where data.visitor.badges contains 'Cart Abandoner'

This appears to return nothing.

What am I missing?

score 0 · Answer 1 · answered Aug 14 '14 at 02:38

0

Unfortunately there's currently no way to query arrays. Your best option is to store it as an object instead.

answered Aug 14 '14 at 02:38

brandonscript

68,675
32
163
220

score 0 · Answer 2 · answered Aug 14 '14 at 16:56

Couple things: I query elements of arrays all the time, but the ql query string is a little temperamental. element = 'string' should return the entire JSON payload if the 'string' is contained anywhere in the array 'element' so the fact you're getting mixed results may be due to the complexity of your nested arrays.

That said, The ql query string allows you to restrict the resources that get returned (like your first example where id = 'xxx'). There isn't any way to return anything other than the entire JSON payload from that resource (such as truncating your array based on the query restriction).

So, if what you're trying to do is pull just the times that your customer returned, I would suggest creating a separate resource called something like "visitorbadges" and connect it to the user record. So instead of querying with the id and trying t query the array you'd have something like:

https://api.usergird.org/{yourorg}/{yourapp}/users/{userid}/vistorbadges

If you use the BaaS userid rather than your own you can go to /users/uuid or, you could also store the userid with the label 'name' ({"name" : "f33498"}) which will let you go to /users/f33498/visitorbadges

See the Apige docs for how to connect resources: http://apigee.com/docs/app-services/content/connecting-users-other-data

How do I query Arrays in Usergrid collections?

2 Answers2