Mongo DB Query on List<Map<String, Map<String, String>>>

Question

i have the sample JSON as below.

{
        "subscriber": {
            "id": "123",
            "custom_field1": "0",
            "custom_field2": "0"
        },
        "subscriptions": [{
            "12": {
                "subs_id": "111",
                "state": "5",
            }
        }, {
            "13": {
                "subs_id": "222",
                "state": "8",
            }, {
            "14": {
                "subs_id": "111",
                "state": "8",
            }
        }]
     }

I am able to query on Mongo DB like

db.testTable.find({"subscriber.id":"123"});

And i am able to get the results too using the above query.

Now i want to filter the data on subscriptions array which consist of "subs_id" = "111".

I tried like below

db.testTable.find({"subscriptions.subs_id":"111"});

which is not giving me results. Can some one suggest me how to do this.

Note: I understand that its in List< Map < String, Map < String, String > > > format and i am querying on value portion of Map object. But not able to get any clue how to fetch the results.

you should try fixing subscriptions by changing it to List of embedded documents. something like this {"subs_key":"12", "subs_id": "111", "state": "5", } and you can find the subscription using db.testTable.find({"subscriber.id":"123", "subscriptions.subs_id":"111"},{"subscriptions.$":1); — s7vr
– s7vr, Commented Dec 30, 2016 at 14:36
agreeing with what sagar said. if mongo would have wild card field name search then there would be a different story but right now your document probably need tweaking. why to label your docs with numbers ? is there a reason ? — satish chennupati
– satish chennupati, Commented Dec 30, 2016 at 15:09
Thanks for inputs. But i can't change the input data format as it is coming from client end. Just need to filter data in the format given.. Any suggestions without data tweaking is appriciated..! — Manindar
– Manindar, Commented Dec 30, 2016 at 15:22
You're just missing the key value 12 in your example. db.testTable.find({"subscriptions.12.subs_id":"111"}) works. — DaveStSomeWhere
– DaveStSomeWhere, Commented Dec 30, 2016 at 15:24
Dear @DaveCoast Please find the updated JSON. There can be multiple values with subs_id=111 but its key can be different. — Manindar
– Manindar, Commented Dec 30, 2016 at 15:30

Rahul Kumar · Accepted Answer · 2016-12-30 16:27:48Z

2

maybe you can use $where

db.testTable.find({$where : "for(var i in this.subscriptions){for(var j in this.subscriptions[i]){if(this.subscriptions[i][j]['subs_id'] && this.subscriptions[i][j]['subs_id'] === '111'){return true;}}}"});

Don't curse me, the db design is faulty imo. You might need to rethink your db structure for efficient and 'pleasant' queries.

$where is also costly, since it doesn't uses index, it's basically a scan on all document hence comparatively slow.

Also I would like to clarify that subscriptions field is basically List<Map<String,Map<String,String>>>

edited Dec 30, 2016 at 16:27

answered Dec 30, 2016 at 16:06

Rahul Kumar

2,8411 gold badge24 silver badges31 bronze badges

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

2 Comments

Manindar Over a year ago

Thanks a lot, Its working fine. can u suggest any link for implementing the similar query using JAVA code.

Rahul Kumar Over a year ago

Well, all java drivers supports $where in one way or other, Which one you plan to use is up to you. in case if you want to use spring-data-mongodb I couldn't find a straightforward way but you can create a BasicQuery with string, in String you can pass the whole clause which we have inside find parameter. as an example see line number 223 : github.com/spring-projects/spring-data-mongodb/blob/master/…

Collectives™ on Stack Overflow

Mongo DB Query on List<Map<String, Map<String, String>>>

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related