Query specific value in a nested JSON using PyMongo

Question

My JSON is as follows:

# Example static JSON payload
request = {"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}

Using PyMongo I want to get menuitem with "value" : "New".

Currently I tried request = {'query': {"menuitem.value": "New"}} and sent this request as requests.get(url, json=request)

At the server-side I am executing the query by doing

cursor = mongo.db[collection_name].find(query)

Now this is returning an empty value {}.

However, when I send the query as

request = {'query': {"menu.popup.menuitem.value": "New"}}

it returns

{'_id': '5c815445d866fc261c400e88',
 'menu': {'id': 'file',
          'popup': {'menuitem': [{'onclick': 'CreateNewDoc()', 'value': 'New'},
                                 {'onclick': 'OpenDoc()', 'value': 'Open'},
                                 {'onclick': 'CloseDoc()', 'value': 'Close'}]},
          'value': 'File'}}

How can I get the menuitem record with 'value':'New' only?

score 0 · Answer 1 · answered Mar 07 '19 at 18:06

I am not sure about pymongo. But I can filter the "request" to get the required "value".

You can iterate through the 'menuitem' key since its a 'list'.

result=request['menu']['popup']['menuitem']
for i in range(len(result)):
   print result[i]["value"]
New
Open
Close

score 0 · Answer 2 · answered Mar 07 '19 at 20:19

You can filter your result using the query projection like below:

from pymongo import MongoClient
from pprint import pprint

client = MongoClient(port=27017)
db = client.sodb1
# Step 2: Create sample data
request = {"menu": {
    "id": "file",
    "value": "File",
    "popup": {
        "menuitem": [
            {"value": "New", "onclick": "CreateNewDoc()"},
            {"value": "Open", "onclick": "OpenDoc()"},
            {"value": "Close", "onclick": "CloseDoc()"}
        ]
    }
}}

db.socoll1.insert_one(request)

# Get entire Doc
cursor = db.socoll1.find({"menu.popup.menuitem.value": "New"})

print('\nEntire Doc is\n')
for doc in cursor:
    pprint(doc)

# add the filter to the projection
cursor = db.socoll1.find({"menu.popup.menuitem.value": "New"}, {"menu.popup.menuitem.$": 1})

print('\nJust what you want is\n')
for doc in cursor:
    pprint(doc)


#outputs
Entire Doc is

{'_id': ObjectId('5c817bf1dbb08c1b51f54d7e'),
 'menu': {'id': 'file',
          'popup': {'menuitem': [{'onclick': 'CreateNewDoc()', 'value': 'New'},
                                 {'onclick': 'OpenDoc()', 'value': 'Open'},
                                 {'onclick': 'CloseDoc()', 'value': 'Close'}]},
          'value': 'File'}}

Just what you want is

{'_id': ObjectId('5c817bf1dbb08c1b51f54d7e'),
 'menu': {'popup': {'menuitem': [{'onclick': 'CreateNewDoc()',
                                  'value': 'New'}]}}}

If the JSON is already in the DB and I want to send the query as a request how should I send it? I'm trying `request = {"query": {{"menu.popup.menuitem.value": "New"},{"menu.popup.menuitem.$": 1}}}` and then `requests.get(url, json=request)` but I get `TypeError: unhashable type: 'dict'`. The server side is extracting the sent query as `query = request.json['query']` and executing it as `cursor = mongo.db[collection_name].find(query)` — , Mar 07 '19 at 23:35
My server side code: `query = request.json['query'] cursor = mongo.db[collection_name].find(query)` I thought of sending the query as a list of dict as `[{"menu.popup.menuitem.value": "New"},{"menu.popup.menuitem.$": 1}]` and then extracting it at the server side as `query = request.json[0] proj= request.json[1] cursor = mongo.db[collection_name].find(query,proj)` This works fine but the problem is that I want to make this dynamic. Any idea how to go about it? — , Mar 08 '19 at 13:25

Query specific value in a nested JSON using PyMongo

2 Answers2