postgresql 使用Postgres在多个列上调用_json

a0zr77ik 于 2023-02-04 发布在 PostgreSQL

关注(0)|答案(3)|浏览(125)

假设我在Postgres中有以下表模式：

CREATE TABLE users (id text, email text, phone_number text);

不管出于什么原因，我都希望将电子邮件和电话号码选择为JSON：

SELECT to_json(users.email, users.phone_number) AS user FROM users WHERE id=usr_123;

我得到了一个如下所示的错误：

function to_json(text, text) does not exist 

No function matches the given name and argument types. You might need to add explicit type casts.

但这样做效果很好：

SELECT to_json(users.*) AS user FROM users WHERE id=usr_123;

如何在Postgres中使用to_json调用只选择几列（而不是全部）？

3条答案

精细手册上说to_json的签名是：

to_json(anyelement)

所以你应该说to_json(one_single_value)。当你说：

to_json(users.email, users.phone_number)

你试图用两个值调用to_json，但是没有这样的to_json函数，当你说：

to_json(user.*)

你实际上是用一个参数来调用to_json，所以它可以正常工作。
您可以按照klin的建议使用派生表或CTE，也可以手动构建ROW：

select to_json(row(users.email, users.phone_number)) ...

这样做的问题是ROW没有任何列名，因此JSON将使用无用的键，如"f1"和"f2"。要解决这个问题，需要将ROW转换为有名称的对象。获取名称的一种方法是create a custom type：

create type email_and_phone_number as (
    email text,
    phone_number text
)

然后将您的ROW转换为该类型：

select to_json(row(users.email, users.phone_number)::email_and_phone_number) ...

您还可以使用临时表代替自定义类型：

create temporary table email_and_phone_number (
    email text,
    phone_number text
)

然后使用与自定义类型相同的强制转换。
如果你经常构建这种特定的JSON格式，那么定制类型就有意义。如果这是一次性的，那么临时表就有意义，临时表会在会话结束时自动消失，所以没有什么需要清理的。当然，派生表或CTE也可能有意义，这取决于查询和你使用什么工具与数据库接口。

使用子查询，例如：

select to_json(sub)
from (
    select email, phone_number
    from users
    where id = 'usr_123'
    ) sub;

或with查询：

with cte as (
    select email, phone_number
    from users
    where id = 'usr_123')
select to_json(cte)
from cte;

如果您只想从结果中删除一个特定字段，有一个优雅的解决方案：

SELECT to_json(users)::jsonb - 'id' AS user FROM users WHERE id=usr_123;