Skip to content
KeystoneJS LogoKeystoneJS (α)

Mongo Join Builder

Perform JOINs in Mongo with a simplified query syntax.

Examples

Given this dataset:

db.items.insert([
  { _id: 1, name: 'almonds', stock: [1, 3] },
  { _id: 2, name: 'pecans', stock: [2] },
  { _id: 3, name: 'cookies', stock: [4, 5] },
]);

// Only item 2 & 3 have stock. Item 2 only has stock in warehouse A.Item 3 used
// to have stock in warehouse B, but now only has stock in warehouse A.
db.warehouses.insert([
  { _id: 1, item: 1, warehouse: 'A', stock: 0 },
  { _id: 2, item: 2, warehouse: 'A', stock: 80 },
  { _id: 3, item: 1, warehouse: 'B', stock: 0 },
  { _id: 4, item: 3, warehouse: 'B', stock: 0 },
  { _id: 5, item: 3, warehouse: 'A', stock: 40 },
]);

db.orders.insert([
  { _id: 1, items: [1], price: 12, ordered: 2, fulfilled: false },
  { _id: 2, items: [2], price: 20, ordered: 1, fulfilled: false },
  { _id: 3, items: [3, 1], price: 10, ordered: 60, fulfilled: false },
  { _id: 4, items: [1], price: 10, ordered: 60, fulfilled: true },
]);

Find unfulfilled orders with items containing 'a' in the name

We can write this query like so:

{
  fulfilled: false,
  items_every: {
    name_contains: 'a'
  }
}

Can also be written with an explicit AND:

{
  AND: [
    { fulfilled: false },
    {
      items_every: {
        name_contains: 'a',
      },
    },
  ];
}

We'd expect the following results:

[
  {
    id: 1,
    items: [
      {
        id: 1,
        name: 'almonds',
        stock: [{ id: 1, warehouse: 'A', stock: 0 }, { id: 3, warehouse: 'B', stock: 0 }],
      },
    ],
  },
  {
    id: 2,
    items: [
      {
        id: 2,
        name: 'pecans',
        stock: [{ id: 2, warehouse: 'A', stock: 80 }],
      },
    ],
  },
];

The raw MongoDB query to do this is complex.

See the MongoDB query
db.orders.aggregate([
  {
    $match: {
      $and: [{ fulfilled: { $eq: false } }],
    },
  },
  {
    $lookup: {
      from: 'items',
      as: 'abc123_items',
      let: {
        abc123_items_items: '$items',
      },
      pipeline: [
        {
          $match: {
            $and: [{ name: { $regex: /a/ } }, { $expr: { $in: ['$_id', '$$abc123_items_items'] } }],
          },
        },
        {
          $addFields: {
            id: '$_id',
          },
        },
      ],
    },
  },
  {
    $addFields: {
      abc123_items_every: { $eq: [{ $size: '$abc123_items' }, { $size: '$items' }] },
      abc123_items_none: { $eq: [{ $size: '$abc123_items' }, 0] },
      abc123_items_some: {
        $and: [
          { $gt: [{ $size: '$abc123_items' }, 0] },
          { $lte: [{ $size: '$abc123_items' }, { $size: '$items' }] },
        ],
      },
    },
  },
  {
    $match: {
      $and: [{ abc123_items_every: { $eq: true } }],
    },
  },
  {
    $addFields: {
      id: '$_id',
    },
  },
]);

Instead, we can use mongo-join-builder!

NOTE: This example is incomplete, and only for documentation purposes. See examples/ for complete examples.

const { mongoJoinBuilder } = require('@keystone-alpha/mongo-join-builder');
const database = require('./my-mongodb-connection');

const builder = mongoJoinBuilder({
  tokenizer: {
    // executed for simple query components (eg; 'fulfilled: false' / name: 'a')
    simple: (qyer, key, path) => ({
      // ... mongo specific syntax for filtering items
      [key]: { $eq: query[key] },
    }),

    // executed for complex query components (eg; items: { ... })
    relationship: (query, key, path, uid) => {
      return {
        from: 'items', // the collection name to join with
        field: 'items', // The field on the 'orders' collection
        // A mutation to run on the data post-join. Useful for merging joined
        // data back into the original object.
        // Executed on a depth-first basis for nested relationships.
        postQueryMutation: (parentObj, keyOfRelationship, rootObj, pathToParent) => {
          // For this example, we want the joined items to overwrite the array
          //of IDs
          return {
            ...parentObj,
            items: parentObj[keyOfRelationship],
          };
        },
        // The conditions under which an item from the 'orders' collection is
        // considered a match and included in the end result
        // All the keys on an 'order' are available, plus 3 special keys:
        // 1) <uid>_<field>_every - is `true` when every joined item matches the
        //    query
        // 2) <uid>_<field>_some - is `true` when some joined item matches the
        //    query
        // 3) <uid>_<field>_none - is `true` when none of the joined items match
        //    the query
        match: { [`${uid}_items_every`]: true },
        // Flag this is a to-many relationship
        many: true,
      };
    },
  },
});

const query = {
  fulfilled: false,
  items: {
    name: 'a',
  },
};

const result = await builder(query, database, 'orders');

Limitations

Due to a limitation in mongo@<4.x, relationship queries will fail silently with 0 results unless IDs are stored as ObjectIds.

Have you found a mistake, something that is missing, or could be improved on this page? Please edit the Markdown file on GitHub and submit a PR with your changes.

Edit Page