postgresql 如何将 array_agg/array_to_json 应用于具有修改列的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13819197/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to apply array_agg/array_to_json to a query with modified columns
提问by hs0
I am using array_to_jsonin combination with array_aggto format certain results in PostgreSQL as JSON. This works fine for queries when I want to return the default value of a query (all columns, unmodified). But I'm stumped how I could use array_aggto create a JSON object for a query where I want to modify some of the output.
我array_to_json结合使用array_agg将 PostgreSQL 中的某些结果格式化为 JSON。当我想返回查询的默认值（所有列，未修改）时，这适用于查询。但是我很困惑如何使用array_agg为我想修改某些输出的查询创建一个 JSON 对象。
Here's an example:
下面是一个例子：
CREATE TABLE temp_user (
user_id serial PRIMARY KEY,
real_name text
);
CREATE TABLE temp_user_ip (
user_id integer,
ip_address text
);
INSERT INTO temp_user (user_id, real_name) VALUES (1, 'Elise'), (2, 'John'), (3, NULL);
INSERT INTO temp_user_ip (user_id, ip_address) VALUES (1, '10.0.0.4'), (2, '10.0.0.7'), (3, '10.0.0.9');
The following query works fine:
以下查询工作正常：
# SELECT array_to_json(array_agg(temp_user)) as users from temp_user;
users
-----------------------------------------------------------------------------------------------------
[{"user_id":1,"real_name":"Elise"},{"user_id":2,"real_name":"John"},{"user_id":3,"real_name":null}]
But let's say that I don't like the null value appearing for user 3. I'd rather see the string "User logged in from $ip" instead.
但是假设我不喜欢用户 3 出现的空值。我宁愿看到字符串“用户从 $ip 登录”。
I can do this:
我可以做这个：
# SELECT user_id, (CASE WHEN real_name IS NULL THEN (select 'User logged in from ' || ip_address FROM temp_user_ip WHERE user_id = temp_user.user_id) ELSE real_name END) as name from temp_user;
And I get the following results:
我得到以下结果：
user_id | name
---------+------------------------------
1 | Elise
2 | John
3 | User logged in from 10.0.0.9
Which is great. But I can't figure out how to manipulate this data into JSON format like the first example.
这很棒。但我无法弄清楚如何像第一个示例一样将这些数据处理为 JSON 格式。
The desired output is:
所需的输出是：
[{"user_id":1,"name":"Elise"},{"user_id":2,"name":"John"},{"user_id":3,"name":"User logged in from 10.0.0.9"}]
This doesn't work:
这不起作用：
# select array_to_json(array_agg ( (SELECT user_id, (CASE WHEN real_name IS NULL THEN (select 'User logged in from ' || ip_address FROM temp_user_ip WHERE user_id = temp_user.user_id) ELSE real_name END) as name from temp_user)));
ERROR: subquery must return only one column
I can't figure out any way to get the data into a format that array_aggaccepts. I even tried creating a custom type which matched the format of temp_user and trying to array_aggcalls to the type constructor, which returned the same error. The error doesn't make sense to me - if the subquery is aggregated, then it shouldn't matter if it returns more than one column. Any advice?
我想不出任何方法将数据转换为array_agg可接受的格式。我什至尝试创建一个与 temp_user 格式匹配的自定义类型，并尝试array_agg调用返回相同错误的类型构造函数。该错误对我来说没有意义 - 如果子查询是聚合的，那么它是否返回多于一列应该无关紧要。有什么建议吗？
回答by dezso
You can separate the aggregate call from the subquery and use the rowconstructor for generating the compound data:
您可以将聚合调用与子查询分开，并使用row构造函数生成复合数据：
SELECT
array_to_json(array_agg(row(t.*))) AS users
FROM
(
SELECT user_id,
CASE
WHEN real_name IS NULL
THEN (
SELECT 'User logged in from ' || ip_address
FROM temp_user_ip
WHERE user_id = temp_user.user_id
) ELSE real_name
END AS name
FROM temp_user
) t
;
You can also check this on SQLFiddle.
回答by Frank Conry
PostgreSQL now has a function json_aggthat can be used in lieu of array_to_json(array_agg( ... ))but actually behaves better in some cases. See "Array_agg in postgres selectively quotes" and the documentation: "Aggregate Functions".
PostgreSQL 现在有一个函数json_agg可以用来代替，array_to_json(array_agg( ... ))但在某些情况下实际上表现得更好。请参阅“ postgres 中的 Array_agg 有选择地引用”和文档：“聚合函数”。
Here is the modified query:
这是修改后的查询：
SELECT
json_agg(row(t.*)) AS users
FROM
(
SELECT user_id,
CASE
WHEN real_name IS NULL
THEN (
SELECT 'User logged in from ' || ip_address
FROM temp_user_ip
WHERE user_id = temp_user.user_id
) ELSE real_name
END AS name
FROM temp_user
) t
;
