0

My database structure is like this

{
  rootField: "value",
  anotherRootField: "value1",
  products:[
     {
       productName: "p1_name",
       currencies: [
         {
            currencyName: "p1c1_name"
         },
         {
           currencyName: "p1c2_name" 
         }
       ]
     },
     {
       productName: "p2_name",
       currencies: [
         {
          currencyName: "p2c1_name"
         },
         {
          currencyName: "p2c2_name"
         },
       ]
     },
   ],
   ships: [
     {
      shipDate: "shipDate"
     },
     ...
   ]
}

For some reason users can send different queries which means I don't know what exactly they want and also they can search for each level of my document.

My goal is to process the query and return the result as a microsoft excel file. for that I need to remove products/currencies/ships if they don't match the query. for example for this query:

{
 productName: "p1_name",
}

I need something like this:

{
  rootField: "value",
  anotherRootField: "value1",
  products:[
     {
       productName: "p1_name",
       currencies: [
         {
            currencyName: "p1c1_name"
         },
         {
           currencyName: "p1c2_name" 
         }
       ]
     }
   ]
}

or if search for currency:

{
 currencyName: "p2c1_name",
}

I need this:

{
  rootField: "value",
  anotherRootField: "value1",
  products:[
     {
       productName: "p2_name",
       currencies: [
         {
          currencyName: "p2c1_name"
         },
       ]
     },
   ]
}

I have a hard time to understanding the aggregation framework and this is what I working on:

await Proforma.aggregate([
        { $match: { rootField: "value" } },// sample query
        { $unwind: "$products" },
        { $match: { "products.productName": "p1_name" } }, // sample query
        { $unwind: "$products.currencies" },
        { $match: { "products.currencies.currencyName": "p1c1_name"}}, // sample query
        {  // back to array
          $group: {
            _id: { _id: "$_id", product__id: "$products._id" },
            currencies: { $push: "$products.currencies" },
          },
        },
        { // back to array
          $group: {
            _id: "$_id._id",
            products: {
              $push: {
                _id: "$id.product__id",
                currencies: "$currencies",
              },
            },
          },
        },
      ]);

As I said my query could be very complex and contains multiple fields from root, products, currencies and ships so I don't know is this works for my situations or not but my current problem is my rootFields get ignored from the result. I just want to filter nested fields and have rootFields untouched.

and also I don't know how to add ships to this query

m z
  • 1
  • Have you tried the array query operator [$elemMatch](https://docs.mongodb.com/v4.2/reference/operator/query/elemMatch/index.html)? – prasad_ Nov 02 '20 at 06:04
  • I use elemMatch with find and works well for just searching (somewhere else). but i need to remove nested arrays if they don't match so i can create my excel rows correctly. i dont know maybe i am wrong and this can be done in deferent way – m z Nov 02 '20 at 06:42
  • You can try using the `$filter` aggregation array operator to iterate the array and filter your array elements. – prasad_ Nov 02 '20 at 06:46
  • It works for products but not for currencies ` { $project: { products: { $filter: { input: "$products", as: "product", cond: { $eq: ["$$product.productName", "name"] }, }, }, "products.currencies": { // does not works $filter: { input: "$products.currencies", as: "currency", cond: { $exists: ["$$currency.currencyId", true] }, }, }, }, } ` – m z Nov 02 '20 at 07:18
  • Then, try `$unwind` and `$filter` - since its a nested array. – prasad_ Nov 02 '20 at 07:21
  • Can you post an example for that? my problems start when i use unwind. – m z Nov 02 '20 at 07:44
  • I think you can get some idea from this post [mongo group query how to keep fields](https://stackoverflow.com/questions/16662405/mongo-group-query-how-to-keep-fields). Try searching for similar posts. – prasad_ Nov 02 '20 at 07:52

0 Answers0