CODEWITHSUNDEEP
mongodbaggregation-framework
Ragnar
Ragnar

Reputation: 161

MongoDB: using field value as field name

I have next sample documents in MongoDB.

db={
  "contracts": [
    {
      "bid": 1, // id in businesses collection
      "type": "A",
      "name": "N1"
    },
    {
      "bid": 1,
      "type": "B",
      "name": "N2"
    },
    {
      "bid": 1,
      "type": "C",
      "name": "N3"
    }
  ],
  "businesses": [
    {
      "id": 1,
      "contract_settings": {
        "A": {
          "price": 100
        },
        "B": {
          "price": 200
        },
        "default": "A"
      }
    }
  ]
}

I want to find contract's price, according contract's type. If contract's type is not in contract_settings, then I should use default value.

For example for current scheme I expect output as

  "contracts": [
    {
      "bid": 1,
      "type": "A",
      "name": "N1",
      "price": 100
    },
    {
      "bid": 1,
      "type": "B",
      "name": "N2",
      "price": 200
    },
    {
      "bid": 1,
      "type": "C",
      "name": "N3",
      "price":100 // because default settings are settings for type "A"
    }
  ]
}

Contract_settings always has some types and 'default' always connected to existing type.

Is it possible to use field value ( contracts.type in scheme) as field name to get settings from businesses.contract_settings?

Note, that contract_settings can contains arbitrary names, so I can't use solution like this similar problem

here is playground

PS. Same problem in postgres can be solved if contract_settings is jsonb field, and using code like this

    ((CASE WHEN businesses.contract_settings::jsonb ? contracts.contract_type::text
            THEN businesses.contract_settings -> contracts.contract_amount::text
            ELSE businesses.contract_settings -> (businesses.contract_settings ->> 'default') END)->>'price')::double precision

Upvotes: 1

Views: 859

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22316

Whenever you want to "iterate" an object in Mongo it gets quite messy as Mongo requires you to transforms that object to an array and use array operations on it.

I recommend reconsidering the contract_setting schema if possible, With that said here's how I would tackle the issue given the current structure:

db.contracts.aggregate([
  {
    $lookup: {
      from: "businesses",
      localField: "bid",
      foreignField: "id",
      as: "businesses"
    }
  },
  {
    $unwind: "$businesses" /**I'm assuming there's always 1.*/
  },
  {
    $addFields: {
      matchedPrice: {
        $reduce: {
          input: {
            $filter: {
              input: {
                $objectToArray: "$businesses.contract_settings"
              },
              as: "setting",
              cond: {
                $eq: [
                  "$$setting.k",
                  "$type"
                ]
              }
            }
          },
          initialValue: null,
          in: "$$this.v.price"
        }
      }
    }
  },
  {
    $addFields: {
      price: {
        $ifNull: [
          "$matchedPrice",
          {
            $reduce: {
              input: {
                $filter: {
                  input: {
                    $objectToArray: "$businesses.contract_settings"
                  },
                  as: "setting",
                  cond: {
                    $eq: [
                      "$$setting.k",
                      "$businesses.contract_settings.default"
                    ]
                  }
                }
              },
              initialValue: null,
              in: "$$this.v.price"
            }
          }
        ]
      }
    }
  },
  {
    $project: {
      price: 1,
      bid: 1,
      type: 1,
      name: 1
    }
  }
])

MongoPlayground

Upvotes: 1

Related Questions