如果道具名称相同，是否存在json_build_object的缩写形式

Question

如果对象名与列名相同，是否有方法删除"id"one_answers"email"部分？

SELECT pu.project_id, json_build_object('id', u.id, 'email', u.email) AS user
FROM projects_users pu 
LEFT JOIN users u ON pu.user_id = u.id 
WHERE project_id=1

类似于：

SELECT pu.project_id, json_build_object (u.id, u.email) AS user
FROM projects_users pu 
LEFT JOIN users u ON pu.user_id = u.id 
WHERE project_id=1

Answer 1

如果您可以在JSONB中获取所有users列，则可以使用：

SELECT pu.project_id, to_jsonb(u) AS user
FROM projects_users pu 
LEFT JOIN users u ON pu.user_id = u.id 
WHERE project_id=1

如果你想从中删除一个或两个密钥，你可以使用to_jsonb(u) - 'password_hash' - 'last_login'

或者，使用只获取所需列的派生表：

SELECT pu.project_id, to_jsonb(u) AS user
FROM projects_users pu 
LEFT JOIN (
select id, email
from users
) u ON pu.user_id = u.id 
WHERE project_id = 1