Tengo una coleccion:

{
  _id: some object id,
  title: "Example",
  region: "US"
}

Quiero ordenar por elementos por ecualización.

Por ejemplo, si region = US, ponga todos los elementos con una región "US" al comienzo de los resultados y después de ellos elementos con una región diferente.

¿Puedo hacer esto en una consulta de mongodb? Si es así, ¿cómo?

0
Vista1nik 1 jul. 2019 a las 10:01

1 respuesta

La mejor respuesta

Se puede hacer, usando tubelización de agregación

Necesitamos las etapas de la tubería $ proyecto y $ sort

Por debajo de la consulta dará la salida deseada

db.collection_name.aggregate([
  {
    $project: { _id: 1, title: 1, region: 1,
      isCountryUS: {
        $cond: { if: {
                      $eq: [ "US", "$region" ]
                     },
                 then: 1,
                 else: 0
        }
      }
     }
  },
  {
    $sort: { isCountryUS: -1, region: 1 }
  },
  {   
    $project: { _id: 1, title: 1, region: 1 }
  }
]);

Tengamos nuestra colección con la muestra de 20 documentos.

{"_id":"5d19bb679735230010737312","title":"Example 1","region":"US"}
{"_id":"5d19bb679735230010737313","title":"Example 2","region":"US"}
{"_id":"5d19bb679735230010737314","title":"Example 3","region":"US"}
{"_id":"5d19bb679735230010737315","title":"Example 4","region":"US"}
{"_id":"5d19bb679735230010737316","title":"Example 5","region":"IND"}
{"_id":"5d19bb679735230010737317","title":"Example 6","region":"IND"}
{"_id":"5d19bb679735230010737318","title":"Example 7","region":"CAN"}
{"_id":"5d19bb679735230010737319","title":"Example 8","region":"CAN"}
{"_id":"5d19bb67973523001073731a","title":"Example 9","region":"UK"}
{"_id":"5d19bb67973523001073731b","title":"Example 10","region":"UK"}
{"_id":"5d19c0e09735230010737320","title":"Example 20","region":"US"}
{"_id":"5d19c0e09735230010737321","title":"Example 12","region":"MEX"}
{"_id":"5d19c0e09735230010737322","title":"Example 17","region":"MEX"}
{"_id":"5d19c0e09735230010737323","title":"Example 14","region":"MEX"}
{"_id":"5d19c0e09735230010737324","title":"Example 15","region":"FRA"}
{"_id":"5d19c0e09735230010737325","title":"Example 16","region":"FRA"}
{"_id":"5d19c0e09735230010737326","title":"Example 13","region":"ARG"}
{"_id":"5d19c0e09735230010737327","title":"Example 18","region":"ARG"}
{"_id":"5d19c0e09735230010737328","title":"Example 19","region":"GER"}
{"_id":"5d19c0e09735230010737329","title":"Example 11","region":"GER"}

Cuando ejecutamos la primera $ proyecto obtenemos el resultado de abajo Puede ver para nuestra conveniencia para obtener nuestro resultado deseado, hemos introducido un nuevo atributo isCountryUS

{"_id":"5d19bb679735230010737312","title":"Example 1","region":"US","isCountryUS":1}
{"_id":"5d19bb679735230010737313","title":"Example 2","region":"US","isCountryUS":1}
{"_id":"5d19bb679735230010737314","title":"Example 3","region":"US","isCountryUS":1}
{"_id":"5d19bb679735230010737315","title":"Example 4","region":"US","isCountryUS":1}
{"_id":"5d19bb679735230010737316","title":"Example 5","region":"IND","isCountryUS":0}
{"_id":"5d19bb679735230010737317","title":"Example 6","region":"IND","isCountryUS":0}
{"_id":"5d19bb679735230010737318","title":"Example 7","region":"CAN","isCountryUS":0}
{"_id":"5d19bb679735230010737319","title":"Example 8","region":"CAN","isCountryUS":0}
{"_id":"5d19bb67973523001073731a","title":"Example 9","region":"UK","isCountryUS":0}
{"_id":"5d19bb67973523001073731b","title":"Example 10","region":"UK","isCountryUS":0}
{"_id":"5d19c0e09735230010737320","title":"Example 20","region":"US","isCountryUS":1}
{"_id":"5d19c0e09735230010737321","title":"Example 12","region":"MEX","isCountryUS":0}
{"_id":"5d19c0e09735230010737322","title":"Example 17","region":"MEX","isCountryUS":0}
{"_id":"5d19c0e09735230010737323","title":"Example 14","region":"MEX","isCountryUS":0}
{"_id":"5d19c0e09735230010737324","title":"Example 15","region":"FRA","isCountryUS":0}
{"_id":"5d19c0e09735230010737325","title":"Example 16","region":"FRA","isCountryUS":0}
{"_id":"5d19c0e09735230010737326","title":"Example 13","region":"ARG","isCountryUS":0}
{"_id":"5d19c0e09735230010737327","title":"Example 18","region":"ARG","isCountryUS":0}
{"_id":"5d19c0e09735230010737328","title":"Example 19","region":"GER","isCountryUS":0}
{"_id":"5d19c0e09735230010737329","title":"Example 11","region":"GER","isCountryUS":0}

Luego, después de ejecutar el $ sort el resultado será modificado como

{"_id":"5d19bb679735230010737312","title":"Example 1","region":"US","isCountryUS":1}
{"_id":"5d19bb679735230010737313","title":"Example 2","region":"US","isCountryUS":1}
{"_id":"5d19bb679735230010737314","title":"Example 3","region":"US","isCountryUS":1}
{"_id":"5d19bb679735230010737315","title":"Example 4","region":"US","isCountryUS":1}
{"_id":"5d19c0e09735230010737320","title":"Example 20","region":"US","isCountryUS":1}
{"_id":"5d19c0e09735230010737326","title":"Example 13","region":"ARG","isCountryUS":0}
{"_id":"5d19c0e09735230010737327","title":"Example 18","region":"ARG","isCountryUS":0}
{"_id":"5d19bb679735230010737318","title":"Example 7","region":"CAN","isCountryUS":0}
{"_id":"5d19bb679735230010737319","title":"Example 8","region":"CAN","isCountryUS":0}
{"_id":"5d19c0e09735230010737324","title":"Example 15","region":"FRA","isCountryUS":0}
{"_id":"5d19c0e09735230010737325","title":"Example 16","region":"FRA","isCountryUS":0}
{"_id":"5d19c0e09735230010737328","title":"Example 19","region":"GER","isCountryUS":0}
{"_id":"5d19c0e09735230010737329","title":"Example 11","region":"GER","isCountryUS":0}
{"_id":"5d19bb679735230010737316","title":"Example 5","region":"IND","isCountryUS":0}
{"_id":"5d19bb679735230010737317","title":"Example 6","region":"IND","isCountryUS":0}
{"_id":"5d19c0e09735230010737321","title":"Example 12","region":"MEX","isCountryUS":0}
{"_id":"5d19c0e09735230010737322","title":"Example 17","region":"MEX","isCountryUS":0}
{"_id":"5d19c0e09735230010737323","title":"Example 14","region":"MEX","isCountryUS":0}
{"_id":"5d19bb67973523001073731a","title":"Example 9","region":"UK","isCountryUS":0}
{"_id":"5d19bb67973523001073731b","title":"Example 10","region":"UK","isCountryUS":0}

Entonces, en esta etapa de ejecución de la tubería, obtuvimos nuestro resultado deseado, pero con un atributo adicional (que introdujimos para obtener este resultado deseado). Podemos filtrarlo proyectando solo los atributos requeridos en la final $ proyecto

Así que nuestro resultado final será

{"_id":"5d19bb679735230010737312","title":"Example 1","region":"US"}
{"_id":"5d19bb679735230010737313","title":"Example 2","region":"US"}
{"_id":"5d19bb679735230010737314","title":"Example 3","region":"US"}
{"_id":"5d19bb679735230010737315","title":"Example 4","region":"US"}
{"_id":"5d19c0e09735230010737320","title":"Example 20","region":"US"}
{"_id":"5d19c0e09735230010737326","title":"Example 13","region":"ARG"}
{"_id":"5d19c0e09735230010737327","title":"Example 18","region":"ARG"}
{"_id":"5d19bb679735230010737318","title":"Example 7","region":"CAN"}
{"_id":"5d19bb679735230010737319","title":"Example 8","region":"CAN"}
{"_id":"5d19c0e09735230010737324","title":"Example 15","region":"FRA"}
{"_id":"5d19c0e09735230010737325","title":"Example 16","region":"FRA"}
{"_id":"5d19c0e09735230010737328","title":"Example 19","region":"GER"}
{"_id":"5d19c0e09735230010737329","title":"Example 11","region":"GER"}
{"_id":"5d19bb679735230010737316","title":"Example 5","region":"IND"}
{"_id":"5d19bb679735230010737317","title":"Example 6","region":"IND"}
{"_id":"5d19c0e09735230010737321","title":"Example 12","region":"MEX"}
{"_id":"5d19c0e09735230010737322","title":"Example 17","region":"MEX"}
{"_id":"5d19c0e09735230010737323","title":"Example 14","region":"MEX"}
{"_id":"5d19bb67973523001073731a","title":"Example 9","region":"UK"}
{"_id":"5d19bb67973523001073731b","title":"Example 10","region":"UK"}
1
Clement Amarnath 1 jul. 2019 a las 08:29