Cloudant/Mango selector for deeply nested JSONs

Question

Let's say some of my documents have the following structure:

{  
   "something":{  
      "a":"b"
   },
   "some_other_thing":{  
      "c":"d"
   },
   "what_i_want":{  
      "is_down_here":[  
         {  
            "some":{  
               "not_needed":"object"
            },
            "another":{  
               "also_not_needed":"object"
            },
            "i_look_for":"this_tag",
            "tag_properties":{  
               "this":"that"
            }
         },
         {  
            "but_not":{  
               "down":"here"
            }
         }
      ]
   }
}

Is there a Mango JSON selector that can successfully select on "i_look_for" having the value "this_tag" ? It's inside an array (i know its position in the array). I'm also interested on filtering the result so I only get the "tag_properties" in the result.

I have tried a lot of things, including $elemMatch but everything mostly return "invalid json".

Is that even a use case for Mango or should I stick with views ?

score 9 · Accepted Answer · edited May 23 '17 at 12:02

With Cloudant Query (Mango) selector statements, you still need to define an appropriate index before querying. With that in mind, here's your answer:

json-type CQ index

{
  "index": {
    "fields": [
      "what_i_want.is_down_here.0"
    ]
  },
  "type": "json"
}

Selector against json-type index

{
  "selector": {
    "what_i_want.is_down_here.0": {
      "i_look_for": "this_tag"
    },
    "what_i_want.is_down_here.0.tag_properties": {
      "$exists": true
    }
  },
  "fields": [
    "_id",
    "what_i_want.is_down_here.0.tag_properties"
  ]
}

The solution above assumes that you always know/can guarantee the fields you want are within the 0th element of the is_down_here array.

There is another way to answer this question with a different CQ index type. This article explains the differences, and has helpful examples that show querying arrays. Now that you know a little more about the different index types, here's how you'd answer your question with a Lucene search/"text"-type CQ index:

text-type CQ index

{
  "index": {
    "fields": [
      {"name": "what_i_want.is_down_here.[]", "type": "string"}
    ]
  },
  "type": "text"
}

Selector against text-type index

{
  "selector": {
    "what_i_want.is_down_here": {
      "$and": [
        {"$elemMatch": {"i_look_for": "this_tag"}},
        {"$elemMatch": {"tag_properties": {"$exists": true}}}
      ]
    }
  },
  "fields": [
    "_id",
    "what_i_want.is_down_here"
  ]
}

Read the article and you'll learn that each approach has its tradeoffs: json-type indexes are smaller and less flexible (can only index specific elements); text-type is larger but more flexible (can index all array elements). And from this example, you can also see that the projected values also come with some tradeoffs (projecting specific values vs. the entire array).

More examples in these threads:

Do these statements work if keys themselves have dot . in them? E.g. if instead "what_i_want" would be "what.i.want"? — Fedor Petrov, Nov 02 '22 at 13:19
Found answer. The dot . in the key should be escaped using two slashes \\ — Fedor Petrov, Nov 07 '22 at 16:05

score 2 · Answer 2 · answered May 01 '17 at 08:01

2

If I'm understanding your question properly, there are two supported ways of doing this according to the docs:

{
    "what_i_want": {
        "i_look_for": "this_tag"
    }
}

should be equivalent to the abbreviated form:

{
    "what_i_want.i_look_for": "this_tag"
}

answered May 01 '17 at 08:01

Jonathan Hall

75,165
16
143
189

This returns 0 docs: i understand that's because `i_look_for` is not a direct property of `what_i_want`, but a member of the array `is_down_here` – zlr May 01 '17 at 11:52
2

Did you try `what_i_want.0.i_look_for`? – Jonathan Hall May 01 '17 at 12:17
2

Very cool, it works. I did try this array index notation but failed, so thanks ! Next question is how can I avoid positionnal arguments but there's a new answer on that, so I should be all set – zlr May 03 '17 at 08:07

Cloudant/Mango selector for deeply nested JSONs

2 Answers2

Linked