Mongodb query, flatten objects with array

Question

A document:

{
   objectId: 35,
   properties: [
     {
       pset: 'A1',
       property: 'Fire',
       value: '60'
     },
     {
       pset: 'B1',
       property: 'Fire',
       value: '70'
     }  
   ]
}

Input to query:

{ 
   psetsWithProperties: [{ pset: 'A1', property: 'Fire'}, { pset: 'abc', property: 'fff'}]
}

The combination of pset and property is always uniq. So there can not be two 'A1' and 'Fire' in the properties collection.

Expected output:

[{
  objectId: 35,
  properties: {
    'A1/Fire': '60'
  }
}]

So the second item (B1, Fire) is not included since it's not in the input.

If there is no match at all, the properties object is empty:

[{
  objectId: 35,
  properties: {}
}]

The MongoDB aggregation framework should be used here.

Something with $in, $group, $unwind I think.

Don't really know where to start.

Does not have to be exactly as in my examples.

Any ideas?

Just to clarify: why objectId: 35 and do you really need to convert from '60' to 60 ? — mickl
– mickl, Commented May 9, 2019 at 16:23

mickl · Accepted Answer · 2019-05-09 16:35:18Z

To match an array against another array you can use $filter with $anyElementTrue and $map. Then to generate JSON key values dynamically you need $arrayToObject operator, try:

db.col.aggregate([
    {
        $project: {
            _id: 0,
            objectId: 1,
            data: {
                $filter: {
                    input: "$properties",
                    as: "docProp",
                    cond: {
                        $anyElementTrue: {
                            $map: {
                                input: [{ pset: 'A1', property: 'Fire'}, { pset: 'abc', property: 'fff'}],
                                as: "paramProp",
                                in: {
                                    $and: [
                                        { $eq: [ "$$docProp.pset", "$$paramProp.pset" ] },
                                        { $eq: [ "$$docProp.property", "$$paramProp.property" ] },
                                    ]
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    {
        $project: {
            objectId: 1,
            properties: {
                $arrayToObject: {
                    $map: {
                        input: "$data",
                        in: {
                            k: { $concat: [ "$$this.pset", "/", "$$this.property" ] },
                            v: "$$this.value"
                        }
                    }
                }
            }
        }
    }
])

Wow, thats fast. Workday has come to an end. I'll try it out tomorrow (and accept answer if it works). Thx!

Collectives™ on Stack Overflow

Mongodb query, flatten objects with array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related