$split (aggregation)

New in version 3.4.

Definition

  • $split
  • Divides a string into an array of substrings based on a delimiter.$split removes the delimiter and returns the resulting substringsas elements of an array. If the delimiter is not found in the string,$split returns the original string as the only element of an array.

$split has the following operator expression syntax:

  1. { $split: [ <string expression>, <delimiter> ] }

FieldTypeDescriptionstring expressionstringThe string to be split. string expressioncan be any valid expression aslong as it resolves to a string. For more information onexpressions, see Expressions.delimiterstringThe delimiter to use when splitting the string expression.delimiter can be any validexpression as long asit resolves to a string.

Behavior

The $split operator returns an array.The <string expression> and <delimiter> inputs must both bestrings. Otherwise, the operation fails with an error.

ExampleResults
  1. { $split: [ "June-15-2013", "-" ] }
  1. [ "June", "15", "2013" ]
  1. { $split: [ "banana split", "a" ] }
  1. [ "b", "n", "n", " split" ]
  1. { $split: [ "Hello World", " " ] }
  1. [ "Hello", "World" ]
  1. { $split: [ "astronomical", "astro" ] }
  1. [ "", "nomical" ]
  1. { $split: [ "pea green boat", "owl" ] }
  1. [ "pea green boat" ]
{ $split: [ "headphone jack", 7 ] }Errors with message:"$split requires an expression that evaluates to a string asa second argument, found: double"
{ $split: [ "headphone jack", /jack/ ] }Errors with message:"$split requires an expression that evaluates to a string asa second argument, found: regex"

Example

A collection named deliveries contains the following documents:

  1. { "_id" : 1, "city" : "Berkeley, CA", "qty" : 648 }
  2. { "_id" : 2, "city" : "Bend, OR", "qty" : 491 }
  3. { "_id" : 3, "city" : "Kensington, CA", "qty" : 233 }
  4. { "_id" : 4, "city" : "Eugene, OR", "qty" : 842 }
  5. { "_id" : 5, "city" : "Reno, NV", "qty" : 655 }
  6. { "_id" : 6, "city" : "Portland, OR", "qty" : 408 }
  7. { "_id" : 7, "city" : "Sacramento, CA", "qty" : 574 }

The goal of following aggregation operation is to find the totalquantity of deliveries for each state and sort the list indescending order. It has five pipeline stages:

  • The $project stage produces documents with two fields,qty (integer) and city_state (array). The $splitoperator creates an array of strings by splitting the cityfield, using a space (" ") as a delimiter.
  • The $unwind stage creates a separate record for eachelement in the city_state field.
  • The $match stage uses a regular expression to filter outthe city documents, leaving only those containing a state.
  • The $group stage groups all the states together and sums theqty field.
  • The $sort stage sorts the results by total_qty indescending order.
  1. db.deliveries.aggregate([
  2.   { $project : { city_state : { $split: ["$city", ", "] }, qty : 1 } },
  3.   { $unwind : "$city_state" },
  4.   { $match : { city_state : /[A-Z]{2}/ } },
  5.   { $group : { _id: { "state" : "$city_state" }, total_qty : { "$sum" : "$qty" } } },
  6.   { $sort : { total_qty : -1 } }
  7. ]);

The operation returns the following results:

  1. { "_id" : { "state" : "OR" }, "total_qty" : 1741 }
  2. { "_id" : { "state" : "CA" }, "total_qty" : 1455 }
  3. { "_id" : { "state" : "NV" }, "total_qty" : 655 }