MongoDB: using field value as field name

Question

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

Tom Slabbaert · Accepted Answer · 2020-07-17 09:52:20Z

1

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

answered Jul 17, 2020 at 9:52

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ragnar Over a year ago

Nice, it is working, thanks. But what do you mean under "reconsidering the contract_setting schema" ?

Tom Slabbaert Over a year ago

I mean saving the data in a different format that will make querying it easier

Collectives™ on Stack Overflow

MongoDB: using field value as field name

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related