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? stackoverflow.com/questions/24006291/… — Simon Berthiaume
– Simon Berthiaume, Commented Mar 10, 2018 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
– acrogenesis, Commented Mar 10, 2018 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
– acrogenesis, Commented Mar 10, 2018 at 17:37

klin · Accepted Answer · 2018-03-10 23:26:45Z

1

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.

answered Mar 10, 2018 at 23:26

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres json get results in one row

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related