我使用array_to_json
和array_agg
将PostgreSQL中的某些结果格式化为JSON。当我想返回查询的默认值(所有列,未修改)时,这对查询来说很好。但是,我很困惑如何使用array_agg
为查询创建一个JSON对象,以便修改一些输出。
这里有一个例子:
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');
以下查询工作正常:
# 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}]
但我不喜欢用户3出现的空值。我宁愿看到字符串"用户从$ip登录"。
我可以做到:
# 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;
我得到以下结果:
user_id | name
---------+------------------------------
1 | Elise
2 | John
3 | User logged in from 10.0.0.9
这太棒了。但我不知道如何像第一个例子那样将这些数据操作成JSON格式。
所需输出为:
[{"user_id":1,"name":"Elise"},{"user_id":2,"name":"John"},{"user_id":3,"name":"User logged in from 10.0.0.9"}]
这不起作用:
# 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
我想不出任何方法将数据转换成array_agg
可以接受的格式。我甚至尝试创建一个与temp_user格式匹配的自定义类型,并尝试array_agg
调用类型构造函数,结果返回了相同的错误。这个错误对我来说没有意义——如果子查询是聚合的,那么它是否返回多个列也没关系。有什么建议吗?
您可以将聚合调用与子查询分离,并使用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
;
您也可以在SQLFiddle上检查这一点。
PostgreSQL现在有一个函数json_agg
,可以用来代替array_to_json(array_agg( ... ))
,但在某些情况下实际上表现得更好。请参阅"Array_agg in postgres选择性引用"和文档:"聚合函数"。
这是修改后的查询:
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
;