Get information from collection in collection in array in collection

Working with Data
,
1

Hi, I’ve beeing working with json files or json format about one year but all the work I’ve done is in Snowflake, but now I’m using SQL in DBeaver and most of all Python. I’ve beeing workin’ all night long with no sleeping and I can’t figure it out how to achieve the goal of gettin’ the information from this collection

[{'_id': 
   ObjectId('6179b174d5611ff2a0d09896'),
  'Visible': True,
  'fechaCreacion': datetime.datetime(2021, 10, 27, 20, 6, 46, 572000),
  'fechaUpdate': datetime.datetime(1, 1, 1, 0, 0),
  'name': 'Cierre-1',
  'fechaOrdenes': datetime.datetime(2021, 10, 28, 20, 6, 46, 572000),
  'usuario': 'auser',
  'jobId': 'jfe56c914ca41450e9bac2147bcd41767',
  'stops': {'value': 
           {'features': [
                     {'attributes': {'ObjectID': 1,
                                         'Name': 'KK--424|san #11 #31, sfe',
                                         'RouteName': '07081211',
       					     'Sequence': 4,
                                         'SnapX': -100.92710374050219,
                                         'SnapY': 25.508205162504694}},
                         {'attributes': {'ObjectID': 2,
                                         'Name': 'MX--461|Lorenzo Garza',
                                         'RouteName': '07081211',
                                         'Sequence': 5,
                                         'SnapX': -100.90922743603264,
                                         'SnapY': 25.450843660104084}},
     {'attributes': {'ObjectID': 3,
       'Name': 'MX--487|aris',
       'RouteName': '07081210',
       'Sequence': 6,
       'SnapX': -100.97002999999995,
...
     {'attributes': {'ObjectID': 6, 'Name': '07081298', 'OrderCount': None}},
     {'attributes': {'ObjectID': 7, 'Name': '07081297', 'OrderCount': None}},
     {'attributes': {'ObjectID': 8,
       'Name': '07081218',
       'OrderCount': None}}]}}}]

This is the example from the collection named “Cierres” and I want to obtain the information of “attributes” just “Name”, “RouteName” and “Sequence”.

Here’s the work I’ve done and can paste all the work from last 24hrs with no sleepin’

import pdmongo as pdm

mongouser = 'user'
mongopsw = 'PsW2.www'
mongohost = '202.mex-east-2.compute.amazonaws.com'
mongodb = 'DataDB'
mongouri = f"mongodb://{mongouser}:{mongopsw}@{mongohost}"
mongourl = f"mongodb://{mongouser}:{mongopsw}@{mongohost}/{mongodb}"

try: 
    client = MongoClient(mongouri)
    print(f"Connected to MongoDB Successfully")
    db = client
    rutas = db['Rutas'] 
    cierres = db['Cierres'] 
        
except ConnectionFailure:
    print(f"Could not connect to MongoDB")
    sys.exit(1)

cursor_rutas = rutas.find()
df_rutas = pd.json_normalize(list(cursor_rutas), max_level = 1)


cursor_cierres = cierres.find({"stops.features.attributes.name":"$exists"},
                              {"stops.features.attributes.Name": 1},
                              {"stops.features.attributes.RouteName": 1},
                              {"stops.features.attributes.Sequence": 1})
df_cierres = pd.json_normalize(cursor_cierres, max_level = 1)

cursor_rutas.close()
cursor_cierres.close()

Define de DataFrame in pandas named “df_rutas” has beeing represented no problem to obtain the information but I want to obtain the DataFrame from collection ‘Cierres’ as mentioned before.

PLEASE!!! I’m beeging for some help!!

2

Field names are case sensitive so either name lower case n

is wrong Name upper case N or

Read the $exists documentation to see what is wrong with your syntax.

The query is the first argument of cierres.find(), currently you have 4 arguments and only

is the query. The second one is the projection. The closing brace after …Name:1 terminates the second parameter so RouteName:1 and Sequence:1 are not part of the projection document. To have all 3 projected try:

{ "stops.features.attributes.Name": 1,
  "stops.features.attributes.RouteName": 1,
  "stops.features.attributes.Sequence": 1 }