Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-20168

Add an option to $unwind to include documents with empty arrays

    • Major Change
    • Quint 9 09/18/15, QuInt A (10/12/15)

      The stage {$unwind: '$field'} currently produces no output documents for an input with any of the following criteria:

      1. field is not present.
      2. field is present, but has a value of null.
      3. field is an empty array

      SERVER-12685 is requesting that condition 1 can optionally produce output. This ticket is requesting that condition 3 can optionally produce output. For example, with a syntax like

        $unwind: {
          path: '$field',
          includeEmpty: <bool>

      The old syntax of {unwind: '$field'} should still be accepted as well.

      This would enable a $lookUp + $unwind to produce a stream of documents that is equivalent to a left outer join. Currently this is not possible as the $lookUp stage produces an empty array for documents which have no matches in the foreign collection. If one were to unwind the as field afterwards, such a non-matching document would not produce any results.

      e.g. If you have these collections:

      {_id: 'tshirt', p: 12}
      {_id: 'shorts', p: 30}
      {_id: 0, product_id: 'tshirt', q: 2}
      {_id: 1, product_id: 'tshirt', q: 1}

      And you use the following pipeline:

          $lookUp: {
            from: 'sales',
            localField: '$_id',
            foreignField: '$product_id',
            as: 'sale'
      // Produces two documents:
      // {_id: 'tshirt', p: 12, sale: [{_id: 0, ...}, {_id: 1, ...} ]}
      // {_id: 'shorts', p: 30, sale: []}
          $unwind: '$sale'

      You'll get the following output:

      {_id: 'tshirt', p: 30, sale: {_id: 0, product_id: 0, q: 2}}
      {_id: 'tshirt', p: 30, sale: {_id: 1, product_id: 0, q: 1}}

      Which does not include any output for the second document, and hence is not an outer join.

      If you replaced the $unwind stage with the proposed

      {$unwind: {field: '$sale', includeEmpty: true}}

      You would instead get the following results:

      {_id: 'tshirt', p: 30, sale: {_id: 0, product_id: 0, q: 2}}
      {_id: 'tshirt', p: 30, sale: {_id: 1, product_id: 0, q: 1}}
      {_id: 'shorts', p: 12, sale: null}

      It may also be desirable to specify a default value in this case rather than always using null

            charlie.swanson@mongodb.com Charlie Swanson
            charlie.swanson@mongodb.com Charlie Swanson
            0 Vote for this issue
            6 Start watching this issue