Build a JSON Database in Node.js (Part 5): Order, Limit & Filter Records

In this part, we'll extend the find() method's capabilities to allow it to sort, limit, and filter the records of a table.

Ready? Let's build!

Order results based on an attribute value

The filters.order property is an optional array of exactly 2 strings in the following format:

find({
  order: ['attribute', 'asc|desc']
})

Where:

• 'attribute' <string>: The name of the attribute whose value will be used to sort the records.
• 'asc' or 'desc': The order in which the records should be sorted, where asc is short for "ascending" and desc is short for "descending".

👉 By default, results are returned in their natural indexed order.

Example

Let's consider the following query:

Products.find({
  order: ['price', 'desc']
});

Which in plain English translates to:

"Retrieve all the records in the products table and order them by price in descending order".

And in SQL to:

SELECT * FROM products ORDER BY price DESC;

Implement attribute ordering

Let's first check that the filters.order property is defined, and throw an error if:

1. It isn't an array or doesn't contain exactly two elements.
2. Its first element isn't a string or a valid attribute.
3. Its second element isn't the string "asc" or "desc".

// ...

export default class Model {
  // ...

  find(filters = {}) {
    // ...

    if (filters.order !== undefined) {
      if (!Array.isArray(filters.order) || filters.order.length !== 2) {
        throw new Error(`Model: find(): "filters.order" must be a two-element array`);
      } else if (typeof filters.order[0] !== 'string' || (results.length && !Object.keys(results[0]).includes(filters.order[0]))) {
        throw new Error(`Model: find(): "filters.order" first element must be a valid attribute`);
      } else if (!['asc', 'desc'].includes(filters.order[1])) {
        throw new Error(`Model: find(): "filters.order" second element must be the string "asc" or "desc"`);
      }
    }

    return results;
  }
}

Let's:

1. Extract the attribute to order by and the desired sorting order from the filters.order array.
2. Convert that sorting order into a numeric direction where 1 means ascending and -1 means descending.

// ...

export default class Model {
  // ...

  find(filters = {}) {
    // ...

    if (filters.order !== undefined) {
      // ...

      const [sortColumn, sortOrder] = filters.order;
      const sortDirection = sortOrder === 'asc' ? 1 : -1;
    }

    return results;
  }
}

Finally, let's use the sort() method of the results array to compare the elements of the results array between them, and reorder them based on the value of the numeric direction previously computed.

// ...

export default class Model {
  // ...

  find(filters = {}) {
    // ...

    if (filters.order !== undefined) {
      // ...

      const [sortColumn, sortOrder] = filters.order;
      const sortDirection = sortOrder === 'asc' ? 1 : -1;

      results = results.sort((a, b) => {
        if (a[sortColumn] === b[sortColumn]) {
          return 0;
        }
        return a[sortColumn] > b[sortColumn] ? sortDirection : -sortDirection;
      });
    }

    return results;
  }
}

💡 The return value of the sort() method's callback function is used to determine the sorting order of elements, where:

• Zero means that a and b are considered equal.
• A negative value means that a should come before b.
• A positive value means that a should come after b.

Limit the number of results

The filters.limit property is an optional, strictly positive integer.

find({
  limit: number
})

Example

Let's consider the following query:

Products.find({
  order: ['price', 'desc'],
  limit: 5
});

Which in plain English translates to:

"Retrieve all the records in the products table, sort them by descending price, and limit the number of results to 5".

And in SQL to:

SELECT * FROM products ORDER BY price DESC LIMIT 5;

Implement results limiting

Let's:

1. Throw an error if the filters.limit property is defined but isn't a positive number.
2. Use the slice() method to only keep the first N elements of the results array.

// ...

export default class Model {
  // ...

  find(filters = {}) {
    // ...

    if (filters.limit !== undefined) {
      if (!Number.isInteger(filters.limit) || filters.limit < 1) {
        throw new Error(`Model: find(): "filters.limit" must be a positive integer`);
      }
      results = results.slice(0, filters.limit);
    }

    return results;
  }
}

Filter out attributes

The filters.attributes property is an optional array of strings in the following format:

find({
  attributes: ['attribute', ...]
})

Where:

• 'attribute', ...: The list of attributes to retrieve for each record.

Example

Let's consider the following query:

Products.find({
  attributes: ['name', 'price']
});

Which in plain English translates to:

"Retrieve the name and price attributes from all the records in the products table".

And in SQL to:

SELECT name, price FROM products;

Implement attributes filtering

Let's throw an error if the filters.attributes property is defined but it isn't an array or is empty.

// ...

export default class Model {
  // ...

  find(filters = {}) {
    // ...

    if (filters.attributes !== undefined) {
      if (!Array.isArray(filters.attributes) || (Array.isArray(filters.attributes) && !filters.attributes.length)) {
        throw new Error(`Model: find(): "filters.attributes" must be a non-empty array`);
      }
    }

    return results;
  }
}

Let's:

1. Use the map() method to iterate over and modify the elements of the results array.
2. Iterate over the list of attributes to keep and throw an error if the attribute is not a valid key of the record object.

// ...

export default class Model {
  // ...

  find(filters = {}) {
    // ...

    if (filters.attributes !== undefined) {
      //...

      results = results.map(result => {
        const attributes = Object.keys(result);

        for (const key of filters.attributes) {
          if (!attributes.includes(key)) {
            throw new Error(`Model: find(): "${key}" is not a valid attribute`);
          }
        }
      });
    }

    return results;
  }
}

Finally, let's copy the specified attributes into a new object named selected and return this object as the new value of the current record.

// ...

export default class Model {
  // ...

  find(filters = {}) {
    // ...

    if (filters.attributes !== undefined) {
      //...

      results = results.map(result => {
        const attributes = Object.keys(result);
        const selected = {};

        for (const key of filters.attributes) {
          if (!attributes.includes(key)) {
            throw new Error(`Model: find(): "${key}" is not a valid attribute`);
          }
          selected[key] = result[key];
        }
        return selected;
      });
    }

    return results;
  }
}

Conclusion

Congratulations!

You now have a complete method that uses a complete query object to retrieve, filter, order, and limit the records from a table.

In the next part, you'll learn how to implement a method for updating records while preventing no-op operations.

Read next: Build a JSON Database in Node.js (Part 6): Update Records & Prevent No-Ops