What to write in pipeline of MangoDb to find one element of 'keys'?

Question

I would like to create a query to find the number of trees whose species name ends by 'um' by arrondissement. My code is here:

from pymongo import MongoClient
from utils import get_my_password, get_my_username
from pprint import pprint


client = MongoClient(
    host='127.0.0.1',
    port=27017,
    username=get_my_username(),
    password=get_my_password(),
    authSource='admin'
)

db = client['paris']
col = db['trees']

pprint(col.find_one())

{'_id': ObjectId('5f3276d8c22f704983b3f681'),
 'adresse': 'JARDIN DU CHAMP DE MARS / C04',
 'arrondissement': 'PARIS 7E ARRDT',
 'circonferenceencm': 115.0,
 'domanialite': 'Jardin',
 'espece': 'hippocastanum',
 'genre': 'Aesculus',
 'geo_point_2d': [48.8561906007, 2.29586827747],
 'hauteurenm': 11.0,
 'idbase': 107224.0,
 'idemplacement': 'P0040937',
 'libellefrancais': 'Marronnier',
 'remarquable': '0',
 'stadedeveloppement': 'A',
 'typeemplacement': 'Arbre'}

I tryed to do it with next lines:

import re

regex = re.compile('um')
pipeline = [
    {'$group': {'_id': '$arrondissement',
                'CountNumberTrees': {'$count': '${'espece': regex}'}
               }
    }
]


results = col.aggregate(pipeline)
pprint(list(results))

But it returns:

File "<ipython-input-114-fba3a8bf5bfd>", line 8
    'CountNumberTrees': {'$count': '${'espece': regex}'}
                                        ^
SyntaxError: invalid syntax

When I check like this, it shows results: '25245'

results = col.count_documents(filter={'espece': regex})
print(results)

Could you help me please to understand what should I put in pipeline?

Best regards

score 1 · Answer 1 · answered Jul 06 '22 at 23:05

Try this syntax for your aggregate query:

The $match stage filters on espace ending in um.

The $group stage counts each returned record grouped by arrondissement

The $project stage is optional but it provides a tidier list of fields.

cursor = col.aggregate([
    {'$match': {'espece': {'$regex': 'um$'}}},
    {'$group': {'_id': '$arrondissement', 'CountNumberTrees': {'$sum': 1}}},
    {'$project': {'_id': 0, 'arrondissement': '$_id', 'CountNumberTrees': '$CountNumberTrees'}}
])

print(list(cursor))

What to write in pipeline of MangoDb to find one element of 'keys'?

1 Answers1