Postgres json get results in one row

Question

This is what I have right now

SELECT json_build_object(concat(name, r_id), 
       json_agg(json_build_array(value,created_at) ORDER BY created_at ASC))
FROM data
group by concat(name, r_id);

What this returns is 3 rows of data, I need them in the same row as a hash

Got:

row1: {"Name1" : [["70.2", "2018-02-19T16:26:08.857134"], ["69.93", "2018-02-19T16:26:16.568789"]}

row2: {"Name2" : [["70.2", "2018-02-19T16:26:08.857134"], ["69.93", "2018-02-19T16:26:16.568789"]}

row3: {"NewName1" : [["70.2", "2018-02-19T16:26:08.857134"], ["69.93", "2018-02-19T16:26:16.568789"]}

Expected

{
    "Name1": [
        ["70.2", "2018-02-19T16:26:08.857134"],
        ["69.93", "2018-02-19T16:26:16.568789"]
    ],
    "Name2": [
        ["70.2", "2018-02-19T16:26:08.857134"],
        ["69.93", "2018-02-19T16:26:16.568789"]
    ],
    "NewName1": [
        ["70.2", "2018-02-19T16:26:08.857134"],
        ["69.93", "2018-02-19T16:26:16.568789"]
    ]
}

Here is a sqlfiddle with this example http://sqlfiddle.com/#!17/0b10d/1

Something like this? https://stackoverflow.com/questions/24006291/postgresql-return-result-set-as-json-array/24006432 — Simon Berthiaume, Mar 10 '18 at 17:13
@GordonLinoff If I remove the group by I get the error that they must be in a GROUP BY because I'm concating them to use `name` and `r_id` as the keys of the hash. If I remove the concat and group_by I do only get one row but not as expected. — acrogenesis, Mar 10 '18 at 17:20
@SimonBerthiaume It's very close to the second result he is looking for but I'm not entirely sure how to make It work here. — acrogenesis, Mar 10 '18 at 17:37

score 1 · Accepted Answer · answered Mar 10 '18 at 23:26

Use the aggregate function json_object_agg():

select json_object_agg(name, details)
from (
    select concat(name, r_id) as name, 
           json_agg(json_build_array(value,created_at) order by created_at asc) as details
    from data
    group by concat(name, r_id)
    ) s

SqlFiddle.

Postgres json get results in one row

1 Answers1