Is there an equivalent to knex.pluck() that will return values for multiple columns?

Question

In How to return a plain value from a Knex / Postgresql query?, I asked about getting just the column values from a Knex query, without a JSON object containing the column name. I was advised to use pluck(fieldName).

That works fine if I return a single column in my query. How can I return multiple columns in similar format? I'm looking for something like pluck([fieldName1, fieldname2]) or pluck(fieldName1, fieldname2) and for each row, returning ["value1", "value2"]

Answer 1

Lets first go over how does knex.js currently implement pluck and then see if we can figure out a working solution which would fit to your needs.

So essentially what pluck does is that it modifies the query to include pluck column in the select statement and then it processes the response using lodash.map to only return an array of pluck field values (instead of Array of result objects). Meaning it is internally running something like lodash.map(resultsArray, pluckColumnName); on the result of the query.

So using tiny code examples this means that:

Regular query:

1. knex('product').limit(10); // regular query
2. SELECT * FROM `product` LIMIT 10 // translates into wildcard select from the table
3. [{id: 1, name: 'foo'}, {...}] // result is Array of Objects with all columns from the table

Regular query with pluck:

1. knex('product').limit(10).pluck('id'); // query with pluck
2. SELECT id FROM `product` LIMIT 10 // translates into specific column select
3. loadsh.map(results, 'id') // knex internally runs loadsh.map on the results
4. [1, 2, 3, ...] // we get array of pluck column values back

If you are looking to pluck multiple columns you just need to change the result map function to instead of returning only a single value to return an array of multiple values. This can be done in multiple ways:

Solution #1:

Easiest way doing this is to manually include the pluck columns in the query and then running regular Array Map on the results. Code would look something like this:

// construct the query, make sure the fields you want to get values for are present
// it's best to include them in the .select statement because you really don't care for other fields
const query = knex('product').select('id', 'name').limit(10);

return query
  .then(results => {
    return results.map(result => [result.id, result.name]);
  })
  .then(pluckedResults => {
    // pluckedResults: [[1, 'foo'], [2, 'bar'], [3, 'etc'], ...]];
    return pluckedResults;
  });

Solution #2: It is possible to extend knex QueryBuilder to have custom functions available on all queries. So we will create multi-pluck.js and define a .multiPluck QueryBuilder extension for knex.

/**
 * multi-pluck.js
 * We are extending knex QueryBuilder to offer multi column pluck feature
 * We add the pluck columns to the original query select statement and process result to return
 * only the values of pluck columns in the same order they are defined
 *
 * Please note that .multiPluck() needs to be last in the chain as it executes the query and breaks
 * query chaining.
 */

const QueryBuilder = require('knex/lib/query/builder');

Object.assign(QueryBuilder.prototype, {
  multiPluck: function multiPluck (...pluckColumns) {
    // add pluck columns to the query.select
    this.select(...pluckColumns);

    // run the query and map results
    return this.then(results => {

      // go over all result Objects
      return results.map(result => {

        // for each result Object, return an array of values for each pluckColumns
        return pluckColumns.map(pluckColumn => {

          // if pluck column includes table name like product.id, only use id field here
          if (pluckColumn.indexOf('.') !== -1) {
            pluckColumn = pluckColumn.split('.').slice(-1)[0];
          }

          // return given column value from this result row
          return result[pluckColumn];
        });
      });
    });
  }
});

And to use it, you would do something like this:

// include the DB connection file which initialises knex connection
const knex = require('./db/connection').knex;

// include our multi-pluck extension
require('./db/multi-pluck');

// example function to get data from DB using the extension
async function getData () {
  return knex('product').limit(10).multiPluck('id', 'name');
}

// run the function
getData()
  .then(pluckedResults => {
    console.log(pluckedResults); // [[1, 'foo'], [2, 'bar'], [3, 'etc'], ...]
  })
  .catch(err => {
    // handle errors as usual
  });

Note that the QueryBuilder extensions could be included inside the connection initialisation file so you would have them available everywhere.

Warning: As the multiPluck actually runs the query, it breaks the knex query chain and has to be the last function called on the query