Concat String by Group

shiyiwan

I want to group records by _id and create a string by combining client_id values.

Here are examples of my documents:

{
  "_id" : ObjectId("59e955e633d64c81875bfd2f"),
  "tag_id" : 1,
  "client_id" : "10001"
}
{
  "_id" : ObjectId("59e955e633d64c81875bfd30"),
  "tag_id" : 1,
  "client_id" : "10002"
}

I'd like to have this output:

{
  "_id" : 1
  "client_id" : "10001,10002"
}
Neil Lunn

You can do it with the aggregation framework as a "two step" operation. Which is to first accumulate the items to an array via $push withing a $group pipeline, and then to use $concat with $reduce on the produced array in final projection:

db.collection.aggregate([
  { "$group": {
    "_id": "$tag_id",
    "client_id": { "$push": "$client_id" }
  }},
  { "$addFields": {
    "client_id": {
      "$reduce": {
        "input": "$client_id",
        "initialValue": "",
        "in": {
          "$cond": {
            "if": { "$eq": [ "$$value", "" ] },
            "then": "$$this",
            "else": {
              "$concat": ["$$value", ",", "$$this"]
            }
          }
        }
      }
    }
  }}
])

We also apply $cond here to avoid concatenating an empty string with a comma in the results, so it looks more like a delimited list.

FYI There is an JIRA issue SERVER-29339 which does ask for $reduce to be implemented as an accumulator expression to allow it's use directly in a $group pipeline stage. Not likely to happen any time soon, but it theoretically would replace $push in the above and make the operation a single pipeline stage. Sample proposed syntax is on the JIRA issue.

If you don't have $reduce ( requires MongoDB 3.4 ) then just post process the cursor:

db.collection.aggregate([
  { "$group": {
    "_id": "$tag_id",
    "client_id": { "$push": "$client_id" }
  }},
]).map( doc =>
  Object.assign(
    doc,
   { "client_id": doc.client_id.join(",") }
  )
)

Which then leads to the other alternative of doing this using mapReduce if you really must:

db.collection.mapReduce(
  function() {
    emit(this.tag_id,this.client_id);
  },
  function(key,values) {
    return [].concat.apply([],values.map(v => v.split(","))).join(",");
  },
  { "out": { "inline": 1 } }
)

Which of course outputs in the specific mapReduce form of _id and value as the set of keys, but it is basically the output.

We use [].concat.apply([],values.map(...)) because the output of the "reducer" can be a "delimited string" because mapReduce works incrementally with large results and therefore output of the reducer can become "input" on another pass. So we need to expect that this can happen and treat it accordingly.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

TOP Ranking

  1. 1

    Failed to listen on localhost:8000 (reason: Cannot assign requested address)

  2. 2

    Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

  3. 3

    How to import an asset in swift using Bundle.main.path() in a react-native native module

  4. 4

    pump.io port in URL

  5. 5

    Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

  6. 6

    BigQuery - concatenate ignoring NULL

  7. 7

    ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

  8. 8

    ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

  9. 9

    Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

  10. 10

    How to remove the extra space from right in a webview?

  11. 11

    java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

  12. 12

    Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

  13. 13

    flutter: dropdown item programmatically unselect problem

  14. 14

    How to use merge windows unallocated space into Ubuntu using GParted?

  15. 15

    Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

  16. 16

    Nuget add packages gives access denied errors

  17. 17

    Svchost high CPU from Microsoft.BingWeather app errors

  18. 18

    Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

  19. 19

    12.04.3--- Dconf Editor won't show com>canonical>unity option

  20. 20

    Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

  21. 21

    maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

HotTag

Archive