Filtering nested arrays of strings with Cloudant Query

Question

I'm trying to come up with a query to filter results from a data structure of three nested arrays of strings. E.g.:

{"field":
    [
        [
            ["str1", "str2"],
            ["str3", "str4"]
        ],
        [
            ["str5", "str6"],
            ["str7", "str8", "str9"]
        ],
        [
            ["str10", "str11", "str12"]
        ]
    ]
}

For a doc to be valid all of the middle arrays need to have at least one inner array that passes a filter to check if certain strings are not contained in it.

Filtering str1 would make the document valid, as ["str3", str4"] would still validate the middle array of it. Filtering for str1 and str3 would have the first middle array not pass and not return the doc.

What I could come up with, which sadly only seems to work in my head, is

{"selector":{                         //comments start at the bottom and go up
 "field":{
  "$not":{                            //negate the outer array result so it passes if all middle arrays passed
   "$elemMatch":{                     //group the negated middle arrays results and fail outer array if all negated middle arrays failed
    "$not":{                          //expose failed middle arrays by negating the result
     "$elemMatch":{                   //group the results and pass middle array if at least one of the inner arrays is valid
      "$not":{                        //negate the results to pass the ones not to be filtered
       "$elemMatch":{"$in":["str1"]}  //find which of the inner arrays contain the strings to be filtered out
}}}}}}}}

This however doesn't return any docs.

Testing for smaller cases, results are as expected while dealing with a single array. For {"field":["str1", "str2", "str3"]}, {"selector":{"$elemMatch":{"$in":["str1"]}}} returns the doc. {"selector":{"$not":{"$elemMatch":{"$in":["str1"]}}}} doesn't.

For one level of nested arrays things broke down. For

{"field":[["str1", "str2"],["str3", "str4"]]}

the selector

{"selector":{
 "fields":{
  "$elemMatch":{
   "$elemMatch":{"$in":["str1"]
}}}}}

returns the doc. But

{"selector":{
 "fields":{
  "$elemMatch":{
   "$not":{
    "$elemMatch":{"$in":["str1"]
}}}}}}

doesn't. The way I figure, "$elemMatch":{"$in":["str1"] would pass ["str1", "str2"] and fail ["str3", "str4"]. Negating that has ["str1", "str2"] failing and ["str3", "str4"] passing. The elemMatch would then pass and return the doc because ["str3", "str4"] passes.

What about $elemMatch, $not, or their interaction goes against what I understood of them? Is there a more ideal way to go about creating this query?

I'm fairly new to Cloudant, and to NoSQL in general, and have been guiding myself through Cloudant's, CouchDB's and MongoDB's documentation and google searches, but for this issue I'm drawing blanks.

Thanks in advance.

Edit(23/02/17): A more concrete example

Those docs will represent recipes and their ingredients, as well as tags representing more information about the ingredients such as possible allergy triggers for users, or things they might want to avoid or seek in general.

{"name":"cake"
"ingredients":
    [
        [
            {"name":"flour", "tags":["gluten", "carbs"]},
            {"name":"gluten free flour substitute", "tags":["carbs"]}
        ],
        [
            {"name":"milk", "tags":["milk", "lactose", "casein"],
            {"name":"lactose free milk substitute", "tags":[]}
        ],
        [
            {"name":"eggs", "tags":["ovalbumin"]
        ]
    ]
}

ingredients being a list of all ingredients involved, the middle array being individual lists for possible substitutes for each ingredient, and the inner array tags being the tags holding the extra info.

In this example, say an user allergic to gluten and lactose wants to query the database for recipes he can make. This doc would be returned, because both flour and milk have substitutes that don't contain those. If someone would like to cut out all carbs on their diet this doc wouldn't be returned, because there's no option in the flour middle array that doesn't contain carbs. The same goes for someone allergic to ovalbumin.

The problem I'm having with Cloudant Query is that I can't seem to figure out how exactly $not and $elemMatch interact with each other in a "$elemMatch":{"$not":{"$elemMatch":{_condition_}}} structure, nor can I see how I'd implement a view/reduce that would allow this kind of filtering through a query.

The only "solution" I can currently see is to get all the docs, or some subset and keep getting more as needed, and filter them on application level, which sounds very unproductive.

I would suggest using map/reduce views for this instead of Cloudant Query. Cloudant/CouchDB requires you to have an index for every query, even with Cloudant Query. I'm not sure if it's possible to create the needed index to run this query using Cloudant Query. — Bradley Holt
– Bradley Holt, Commented Feb 22, 2017 at 21:49
Also, it would be very helpful if you could provide a more concrete example, rather than abstracting the question away using "str1", "str2", etc. — Bradley Holt
– Bradley Holt, Commented Feb 22, 2017 at 21:52
Do you have any suggestions for how to implement that? I can only think of having complex keys representing every combination of inner arrays, but then how would one query it? group_level won't help since they're all on the same level, and I can't seem to find a solution to query for keys that exclude a set of values. — Rodrigo Bueno
– Rodrigo Bueno, Commented Feb 22, 2017 at 21:54
Alright. I'll see about typing out a more concrete example once I can. I'm currently on the road. If it helps in the mean time, I'm trying to find the docs that have none of the filtered strings in at least one combination of inner arrays. — Rodrigo Bueno
– Rodrigo Bueno, Commented Feb 22, 2017 at 21:56

Bradley Holt · Accepted Answer · 2017-02-24 01:12:51Z

0

I don't think this is entirely there yet, but try a view with a map function like this with your recipes example:

function(doc) {
  doc.ingredients.map(function(ingredient) {
    ingredient.map(function(variant) {
      variant.tags.map(function(tag) {
        emit(tag);
      });
    });
  });
}

This will provide you with a view/index of all tags. This would allow you to find recipes with gluten or with carbs, but I don't think it would allow you to find recipes without gluten or carbs. But, you should be able to build on this to create a view/index for these negating scenarios.

answered Feb 24, 2017 at 1:12

Bradley Holt

6085 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filtering nested arrays of strings with Cloudant Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related