A table (in fact a view which is already the result of some JSON aggregation) has a column of keys, and a column of values that are in JSON format:
+-----------+------------------------------------------------------------+
| user_id | user_data_json |
+-----------+------------------------------------------------------------+
| daveb1985 | {lastlogin:1732605022730, posts_read:[18, 23, 45], etc...} |
| trish2003 | {lastlogin:1732604033135, posts_read:[101], etc...} |
| q2342rte | {lastlogin:1731302284832, posts_read:[], etc...} |
+-----------+------------------------------------------------------------+
I would like to return a JSON object like this:
{
daveb1985: {
lastlogin: 1732605022730,
posts_read: [18, 23, 45]
},
trish2003: {
lastlogin: 1732604033135,
posts_read: [101]
},
q2342rte: {
lastlogin: 1731302284832,
posts_read: []
}
}
The function json_object ( keys text[], values text[] ) → json, combined with array_agg is close to what I want, but it requires the values to be text, whereas my values have type JSON.
I can cast the values to text, but then object values are strings, not JSON objects, which are cumbersome to return to JSON in the client:
SELECT
json_object(
array_agg(user_id), array_agg(CAST(user_data_json AS TEXT))
) AS result
FROM userpostsjson;
Is there any way to construct the JSON object directly?
You are looking for the json_object_agg or jsonb_object_agg function, which creates a single object (per grouping) using a key-value pair.
SELECT
jsonb_object_agg(upj.user_id, upj.user_data_json)
FROM userpostsjson upj;