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:

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

      And you use the following pipeline:

      db.products.aggregate([
        {
          $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

            Assignee:
            charlie.swanson@mongodb.com Charlie Swanson
            Reporter:
            charlie.swanson@mongodb.com Charlie Swanson
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: