如何从Knex/Postgresql查询返回纯值? [英] How to return a plain value from a Knex / Postgresql query?

问题描述

我正在尝试使用Knex从Postgres DB返回一个简单的标量字符串值.到目前为止，我所做的所有操作都返回一个带有键(列名)和值的JSON对象，因此我必须进入该对象才能获取该值.如果返回多行，则会得到多个JSON对象，每个对象都重复键.

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.

我可能返回多列，在这种情况下，每一行至少需要是一个数组.我不是在寻找特殊情况，即指定单个列将返回不包含数组的值-我可以进入数组.我想避免将列名重复列出作为键的JSON对象.

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.

我已经搜索了Knex文档，但看不到如何控制输出.

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)
)

当我使用

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

我得到{"sf_id":"a4T8A0000009PsfUAE"};我想要的只是"a4T8A0000009PsfUAE"

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}'`);

我得到了一个描述结果的更大的JSON对象:

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.)

推荐答案

看看 pluck 方法.

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

这篇关于如何从Knex/Postgresql查询返回纯值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！