MongoDb aggregation query: for every array count the number of properties of nested object

Question

I have documents with the following structure:

"_id" : "AQ106_2020-09-12T09",
"date" : "2020-09-12T09:00:00.000Z",
"station" : {
    "name" : "AQ106",
    "loc" : {
        "type" : "Point",
        "coordinates" : [
            14.339263,
            40.814224
        ]
    },
    "properties" : {
        
    }
},
"samples" : [
    {
        "t" : ISODate("2020-09-12T11:02:00.000+02:00"),
        "data" : {
            "pm1_mg_m3" : 2.7,
            "pm2_5_mg_m3" : 4.6,
            "pm10_mg_m3" : 12,
            "P0" : 152,
            "P1" : 16,
            "P2" : 4.7,
            "P3" : 0.8,
            "P4" : 0.86,
            "P5" : 0.6,
            "P6" : 0.28,
            "P7" : 0.152,
            "P8" : 0.094,
            "P9" : 0.092,
            "P10" : 0.019,
            "P11" : 0,
            "P12" : 0,
            "P13" : 0.0188,
            "P14" : 0,
            "P15" : 0,
            "P16" : 0,
            "P17" : 0,
            "P18" : 0,
            "P19" : 0,
            "P20" : 0,
            "P21" : 0,

                "P22" : 0,
                "P23" : 0,
                "temp_celsius" : 32.59,
                "humRelPercent" : 34,
                "press_mBar" : 1010.79,
                "CO2mA" : 4,
                "NO2_WE_mV" : 226.419,
                "NO2_AE_mV" : 229.553,
                "OX_WE_mV" : 252.287,
                "OX_AE_mV" : 220.419,
                "CO_WE_mV" : 509.077,
                "AE_WE_mV" : 348.51,
                "batt_V" : 13.5,
                "source_V" : 17.6
            }
        },
        .... additional arrays
}

Each document in the collection has a samples field that is an array of objects, each having a t field of type ISODate and a data field of type object containing sensors readings. I would like to count the number of properties into object samples.data for each element in samples and filter documents having this count == N. How can do that? Thanks.

varman · Accepted Answer · 2020-09-14 15:50:30Z

1

You need to do a small change that is to use filter instead of map

{
    $project: {
      station: "$station.name",
      "n": {
        $filter: {
          input: "$samples.data",
          cond: {
            $eq: [
              {
                $size: {
                  "$objectToArray": "$$this"
                }
              },
              39
            ]
          }
        }
      }
    }
  }

Working Mongo playground

answered Sep 14, 2020 at 15:50

varman

8,9226 gold badges25 silver badges56 bronze badges

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

2 Comments

EanX Over a year ago

Thanks, but it does not work as expected. If you only add another doc with less then 39 properties into samples.data it returns both the docs. See here

varman Over a year ago

@EanX mongoplayground.net/p/MJJdTm1wHxb does this help you?

EanX · Accepted Answer · 2020-09-14 14:02:34Z

0

I partially figured it out using the aggregation framework as:

db.collection.aggregate([{$project: {
  station:'$station.name',
  n:{$map:
  {
    input:"$samples.data",
    as:"vals",
    in:  {
        $size: {
          "$objectToArray": "$$vals"
        }
      }

      }}
}}])

But how to filter documents?

answered Sep 14, 2020 at 14:02

EanX

5056 silver badges26 bronze badges

Collectives™ on Stack Overflow

MongoDb aggregation query: for every array count the number of properties of nested object

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related