How to return a plain value from a Knex / Postgresql query?

Question

I'm trying to return a simple, scalar string value from a Postgres DB using Knex. So far, everything I do returns a JSON object with a key (the column name) and the value, so I have to reach into the object to get the value. If I return multiple rows, then I get multiple JSON objects, each one repeating the key.

I could be returning multiple columns, in which case each row would at least need to be an array. I'm not looking for a special case where specifying a single column returns the value without the array -- I'm OK reaching into the array. I want to avoid the JSON object with the repetitive listing of column names as keys.

I've scoured the Knex docs but don't see how to control the output.

My table is a simple mapping table with two string columns:

CREATE TABLE public._suite
(
    piv_id character(18) NOT NULL,
    sf_id character(18)  NOT NULL,
    CONSTRAINT _suite_pkey PRIMARY KEY (piv_id)
)

When I build a query using Knex methods like

let myId = 'foo', table = '_suite';
return db(table).where('piv_id', myId).first(['sf_id'])
                .then( function(id) { return(id); });

I get {"sf_id":"a4T8A0000009PsfUAE"} ; what I want is just "a4T8A0000009PsfUAE"

If I use a raw query, like

return db.raw(`select sf_id from ${table} where piv_id = '${myId}'`);

I get a much larger JSON object describing the result:

{"command":"SELECT","rowCount":1,"oid":null,"rows":[{"sf_id":"a4T8A0000009Q9HUAU"}],"fields":[{"name":"sf_id","tableID":33799,"columnID":2,"dataTypeID":1042,"dataTypeSize":-1,"dataTypeModifier":22,"format":"text"}],"_parsers":[null],"RowCtor":null,"rowAsArray":false}

What do I have to do to just get the value itself? (Again, I'm OK if it's in an array -- I just don't want the column names.)

Answer 1

Take a look at the pluck method.

db(table).where('piv_id', myId).pluck('sf_id'); // => will return you ["a4T8A0000009PsfUAE"]