I am using array_to_json
in combination with array_agg
to 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_agg
to create a JSON object for a query where I want to modify some of the output.
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.
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.
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_agg
accepts. I even tried creating a custom type which matched the format of temp_user and trying to array_agg
calls 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?
You can separate the aggregate call from the subquery and use the row
constructor for generating the compound data:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With