1

Good morning, I am learning MongoDB but when trying to make a query I do not achieve the desired results.

The structure of the documents used is as follows:

use empresa

db.departamento_dos.insert (
{
 "Codigo": 2,
 "Nombre": "Contabilidad",
 "Presupuesto": 10000,
 "Empleados": 
 {
  "Empleado": { "DNI": "10340", "Nombre": "Andres", "Apellido": "Lopez"},
  "Empleado": { "DNI": "10341", "Nombre": "Paula", "Apellido": "Tovar"},
  "Empleado": { "DNI": "10342", "Nombre": "Andres", "Apellido": "Perez"},
  "Empleado": { "DNI": "10343", "Nombre": "Camila", "Apellido": "Perez"},
  "Empleado": { "DNI": "10344", "Nombre": "Cristian", "Apellido": "Forero"}
 }
}
)

and

db.departamento_tres.insert (
{
 "Codigo": 77,
 "Nombre": "Marketing",
 "Presupuesto": 30000,
 "Empleados": 
 {
  "Empleado uno": { "DNI": "10350", "Nombre": "Maria", "Apellido": "Perez"},
  "Empleado dos": { "DNI": "10351", "Nombre": "Angie", "Apellido": "Gómez"},
  "Empleado tres": { "DNI": "10352", "Nombre": "Andrea", "Apellido": "Lopez"},
  "Empleado cuatro": { "DNI": "10353", "Nombre": "Cristina", "Apellido": "Riaño"},
  "Empleado cinco": { "DNI": "10354", "Nombre": "Annie", "Apellido": "Castellanos"}
 }
}
)

My question is: how can I make a query by "Apellido" by eliminating repeated "Apellidos"?

I tried to use the different operator with a regular expression to auto-complete the query, but not work.

db.departamento_uno.distinct( "Empleados.Empleado", { /.*".Apellido"} )

and it always shows me an empty vector.

The idea is to be able to consult the two documents simultaneously. For example, the result I'm looking for would be:

[ "Lopez", "Tovar", "Perez", "Forero", "Gómez", "Riaño", "Castellanos"]

since the idea is to show each surname only once no matter how many times it appears

Any idea how you can make this query?

Thanks for the help.

luis felipe
  • 23
  • 5
  • Possible duplicate of [Mongo Distinct Aggegation](https://stackoverflow.com/questions/25148955/mongo-distinct-aggegation) –  Feb 16 '18 at 12:22
  • Why is your documents have different schema ( Empleado vs Empleado uno )? Is it by design ? It would be easier to query if you can follow same schema for all documents. – s7vr Feb 16 '18 at 12:48
  • @Veeram Yes, it is by design since the idea is to be able to modify a scheme without affecting the rest. Although as I am new, I do not know if it is the most optimal or convenient. – luis felipe Feb 16 '18 at 12:56
  • Can you change `Empleados` to array ? Something like `"Empleados": [ { "DNI": "10350", "Nombre": "Maria", "Apellido": "Perez"}, { "DNI": "10351", "Nombre": "Angie", "Apellido": "Gómez"}, ... ]`. This makes it easy to query and you can get distinct values using `db.departamento_uno.distinct( "Empleados.Empleado.Apellido")` – s7vr Feb 16 '18 at 12:58
  • @Veeram Yes, I could change it, as long as it respects that they belong to a different department. But if I unify all my arrangement of employees as I would identify those belonging to the same department? – luis felipe Feb 16 '18 at 13:05
  • Sorry, I meant `db.departamento_uno.distinct( "Empleados.Apellido")` earlier. Yes, so would have something like `[{ "Codigo": 77, "Nombre": "Marketing".... "Empleados": [ { "DNI": "10350", "Nombre": "Maria", "Apellido": "Perez"}, { "DNI": "10351", "Nombre": "Angie", "Apellido": "Gómez"}, ... ]},{ "Codigo": 2... }]` – s7vr Feb 16 '18 at 13:12
  • @Veeram Thank you, that form works for the moment, but was there a way to execute the query in the schemas at the same time to only show the "Apellido" once? – luis felipe Feb 16 '18 at 13:19
  • Not sure I follow you. Can you give me an example ? Please also refer to documentation for learning. – s7vr Feb 16 '18 at 13:23
  • @Veeram For example, in department two and three the surname "perez" appears 3 times; if I execute the query separately in each of the documents it will be sent once, it is possible to filter two schemes at the same time to avoid this. I have been reading the documentation and I do not find anything about this topic. Thanks for the help. – luis felipe Feb 16 '18 at 13:38
  • Np. I'm still not clear. What query do you execute ? distinct query ? `db.departamento_uno.distinct( "Empleados.Apellido")` doesn't return what you need ? It gets the distinct values across entire collection. – s7vr Feb 16 '18 at 15:40
  • @Veeram When I execute the query `db.departamento_uno.distinct( "Empleados.Apellido")` and the query `db.departamento_dos.distinct( "Empleados.Apellido")` the query gives me the "Apellidos" separately. My question is Is there a way to join these separate queries into one so that only the data appears once? My question is Is there a way to join these separate queries into one so that only the data appears once? Example: Apellido: Perez appears in both schemes; what I'm looking for is that as it appears in the first schema, it does not appear again when I execute the query in the second schema. – luis felipe Feb 16 '18 at 16:00
  • I thought you just had one collection all this time. Accept my sincere apologies. Do you have any join criteria for two collection ? If no separate queries is the only way. – s7vr Feb 16 '18 at 16:03
  • @Veeram My join criteria is the "DNI" of each employee. Reading the documentation I found the operator $lookup but I don't understand how I could apply it in my query to make a join between the two collections. – luis felipe Feb 16 '18 at 16:09
  • So $lookup is not applicable here. That doesn't look like a join criteria. You have different values in each collection. It looks to me it just two separate collection storing the similar document you should just do two distinct queries and perform another distinct on the client side against two collection results. – s7vr Feb 16 '18 at 16:15
  • @Veeram I think it would be the most optimal solution, so I will. Thanks for the help. But in the case that in the "DNI": were the same values ​​in some cases, could I apply $lookup to effect the union? – luis felipe Feb 16 '18 at 16:20
  • Np. Yes you can. – s7vr Feb 16 '18 at 16:33

0 Answers0