MongoDB sort by value in nested array

Question

I have a collection called datos_sensores which documents store data from 2 sensors, one for temperature and humity and other one for CO2 and consume. There are 2 location for those sensors, location_id:1 and location_id:2. The documents has the following structure, in example:

{
    _id: ObjectId("632eccf6c4c33123d451e23f"),
    timestamp: '2020-07-02T17:45:00Z',
    sensor_id: 1,
    location_id: 2,
    medidas: [
      { tipo_medida: 'Temperatura', valor: 36.76, unidad: 'ºC' },
      { tipo_medida: 'Humedad_relativa', valor: 3.77, unidad: '%' }
    ]
  }

I want to get the minimum value of location_id:2 of the element "valor" inside of the array "medidas". However, if you pay attetion, there are 2 valor fields. I want to get the minimum Temperature value, and not the minimum Humity value (Humedad_relativa). I tried the following:

sensores_IoT> db.datos_sensores.find({"medidas.tipo_medida":"Temperatura",location_id:2}).sort({"medidas.valor":1}).limit(1)

And got this answer:

[
  {
    _id: ObjectId("632ecd14c4c33123d451e67d"),
    timestamp: '2020-07-05T14:00:00Z',
    sensor_id: 1,
    location_id: 2,
    medidas: [
      { tipo_medida: 'Temperatura', valor: 39.51, unidad: 'ºC' },
      { tipo_medida: 'Humedad_relativa', valor: 0.3, unidad: '%' }
    ]
  }
]

As you can see, I am getting the list sorted by humity and not for temperature. How can I tell to mongo to sort by the value tipo_medida.valor of the Temperature?

After that, I need to use $mul to multiply the value by 1.2, but I think it's quite easy to do once I have the objected filtered.

Thank you very much in advance.

Does your medidas array only contain two elements every time? — Charchit Kapoor
– Charchit Kapoor, Commented Oct 6, 2022 at 8:54
Hello Charchit. Yes.. its measures from 2 sensores that are storing data every 15 minuts during 14 days. Here is another document soy you can have an idea: { _id: ObjectId("632ecce8c4c33123d451df95"), timestamp: '2020-07-01T03:15:00Z', sensor_id: 1, location_id: 1, medidas: [ { tipo_medida: 'Temperatura', valor: 14.59, unidad: 'ºC' }, { tipo_medida: 'Humedad_relativa', valor: 88.76, unidad: '%' } ] }, — José Siles Blanco
– José Siles Blanco, Commented Oct 6, 2022 at 9:25
@CharchitKapoor and every time only contains "tipo_medida", "valor" and "unidad" — José Siles Blanco
– José Siles Blanco, Commented Oct 6, 2022 at 9:27
How many sensors do you have? Why are you only looking at location_id:2 in this question? How often do these sensors capture data? — user20042973
– user20042973, Commented Oct 6, 2022 at 12:36
Hi, @user20042973. Sensor_id 1 and sensor_id 2. I am only looking into location_id:2 because the main problem is to find the minimum temperature for that sensor, sorry for didnt specify that. The sensors capture data every 15 min during 14 days. Starting july the first. — José Siles Blanco
– José Siles Blanco, Commented Oct 6, 2022 at 16:18

Charchit Kapoor · Accepted Answer · 2022-10-06 09:48:32Z

1

This should work, here we are first sorting by medidas.tipo_medida, and then by medidas.valor:

db.datos_sensores.find({"medidas.tipo_medida":"Temperatura",location_id:2})
                 .sort({"medidas.tipo_medida": -1,"medidas.valor":1}).limit(1)

answered Oct 6, 2022 at 9:48

Charchit Kapoor

9,3242 gold badges11 silver badges26 bronze badges

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

6 Comments

José Siles Blanco Over a year ago

That works perfectly fine! However, I would like to understand more in deep the sort({"medidas.tipo_medida": -1. Is it used because 'Temperatura' starts with 'T' and 'Humedad' with 'H', right? Thank you very much!

Charchit Kapoor Over a year ago

Yes. First, we sort by medidas.tipo_medida in descending order, and for equal values of medidas.tipo_medida we sort by medidas.valor in ascending order.

José Siles Blanco Over a year ago

Everything clear now. Thank you very much again. I can not give "this answer was useful" due to my reputation level... but, please, consider it as done :P.

Charchit Kapoor Over a year ago

No issues. But where exactly this answer was useful comes, usually people mark the answer as correct, and that's it. Where does this shows up?

José Siles Blanco Over a year ago

Ah, just realized that there are 2 types of feedback: -Tickout: Accept this answer -Arrow up: This answer is useful. Just marked your answer as accepted since is the only one that the pages let to me, for now

|

Collectives™ on Stack Overflow

MongoDB sort by value in nested array

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related