Find all option with only one price in MongoDB-CodePudding

I'm looking for find all option with only one prices I have two collections, collection options and collection prices

Collection options is structured like this for example:

{
  "_id": "option_0",
  "id": 0,
  "type": "option",
  "name": "Stewart",
  "price_per_day": true,
  "sell_per": "person",
  "sell_per_unit_min_capacity": null,
  "sell_per_unit_max_capacity": null,
  "unit_type": "boardType",
  "age_ranges": [
    {
      "id": 0,
      "type": "age_range",
      "age_min": 88,
      "age_max": 33
    },
    {
      "id": 1,
      "type": "age_range",
      "age_min": 47,
      "age_max": 53
    },
    {
      "id": 2,
      "type": "age_range",
      "age_min": 78,
      "age_max": 54
    }
  ],
  "prices": [
    "price_54",
    "price_824",
    "price_811",
    "price_19",
    "price_130",
    "price_855",
    "price_437",
    "price_131"
  ],
  "stocks": [
    "stock_361",
    "stock_111",
    "stock_267",
    "stock_382",
    "stock_345",
    "stock_262",
    "stock_54",
    "stock_718"
  ]
}

Collection prices is structured like this:

{
  "_id": "price_0",
  "id": 0,
  "type": "price",
  "is_archived": true,
  "name": "Wendi",
  "bu": "fr",
  "currency": "EUR",
  "price_type": "duration",
  "commission": "$2,426.70",
  "is_commissionable": true,
  "data": [
    {
      "date": "2022-02-13T01:29:29",
      "durations": "Glenna Merritt",
      "prices": [
        {
          "age_range_id": "age_range_0",
          "value": 22,
          "price_tier": [
            {
              "quantity": 7,
              "value": 11
            }
          ]
        },
        {
          "age_range_id": "age_range_2",
          "value": 28
        }
      ]
    },
    {
      "date": "2022-07-26T01:14:43",
      "durations": "Deanna Blair",
      "prices": [
        {
          "age_range_id": "age_range_0",
          "value": 5,
          "price_tier": [
            {
              "quantity": 10,
              "value": 6
            }
          ]
        },
        {
          "age_range_id": "age_range_1",
          "value": 9
        }
      ]
    }
  ]
}

On the collection price you can have in data multiple Object or just one object.

I will like find all option.prices contains one price on prices.

How can I do that in MongoDB ?

Thanks for helping

I have try to stock all prices with only one price in a var like this:

var pricesWithOnePrice = db.prices.find( {"data": { $size: 1 }} )

And I tried to find all option contain this prices like this:

db.options.find({"prices": pricesWithOnePrice})

I've tried another request but don't work:

db.option.aggregate([{$lookup: {from: "prices", localField: "prices", foreignField: "_id", pipeline: [{$match: {"data": {$size:1}}, as: "jointure"}}])

But I the query run but never get me a result.

CodePudding user response：

One option is to use a $lookup pipeline to bring only the _id of prices that have on item under data, and $match the option with at least one price like this

db.options.aggregate([
  {$lookup: {
      from: "price",
      let: {prices: "$prices"},
      pipeline: [
        {$match: {
            $expr: {$and: [
                {$eq: [{$size: "$data"}, 1]},
                {$in: ["$_id", "$$prices"]}
              ]
            }
        }},
        {$project: {_id: 1}}
      ],
      as: "priceObj"
    }
  },
  {$match: {"priceObj.0": {$exists: true}}}
])

See how it works on the playground example