MongoDB query to aggregate by length of comma-delimited string

Question

I have JSON documents that collect metadata about specific searches within a SaaS application. Here's a sample document:

{
    "startSearch": "2021-04-23T15:12:59.161Z",
    "endSearch": "2021-04-23T15:13:00.501Z",
    "elapsedTime": 1.34,
    "totalRecordsFound": 224,
    "specialSearch": "Test",
    "AccountStatus": "Active, Bankruptcy, Out For Repo, Repossessed",
    "AccountType": "",
    "Location": "",
    "LastName": "",
    "FirstName": "",
    "AccountId": "",
    "Phone": ""
    }

As you can see, the AccountStatus field is a comma-delimited string that should probably be an array. I would like to analyze search results based on how long such an array might be. The pseudocode of such a solution would be something like this:

db.getCollection('Baselining').aggregate([
    {
        $match: {
            "specialSearch": { $ne: "" }, 
            "AccountStatus.split(',').length": { $gt: 1 }
        }
    },
    {
        $group: {
            _id: null,
            averageSearchDuration: { $avg: "$elapsedTime"}
        }
    }
])

Two questions, then: how can I convert the comma-delimited fields into arrays, and how can I group the aggregation by the length of the array? TIA for your help!

I should have searched a little harder for this solution! To convert string to array, I followed the answer by Efan Du at https://stackoverflow.com/questions/46600935/mongodb-convert-string-to-array. For aggregation and grouping, I followed @one_cent_thought's answer at https://stackoverflow.com/questions/7811163/query-for-documents-where-array-size-is-greater-than-1/15224544. — Nosnetrom, Apr 27 '21 at 19:54

score 0 · Answer 1 · answered Apr 27 '21 at 19:56

You can do it, the trick is to use $expr to allow using aggregation expression inside $match stage. Then use $split operator to split your string from ',' separator in an array of strings.

Here's the aggregation :

db.collection.aggregate([
  {
    $match: {
      $and: [
        {
          "specialSearch": {
            $ne: ""
          }
        },
        {
          $expr: {
            $gt: [
              {
                $size: {
                  "$split": [
                    "$AccountStatus",
                    ","
                  ]
                }
              },
              1
            ]
          }
        }
      ],  
    }
  },
  {
    $group: {
      _id: null,
      averageSearchDuration: {
        $avg: "$elapsedTime"
      }
    }
  }
])

You can test it here

MongoDB query to aggregate by length of comma-delimited string

1 Answers1