Return a json of multiple key-value pairs instead of list of single-paired key-value jsons

Question

Currently my query looks like follows and returns the results below:

select
    c.id as company_id,
    json_agg(json_build_object(ds.statement_ref, value)) as financials
from
    st.data_statements ds
    join st.company_data cd on ds.company_datum_id = cd.id
    join st.companies c on cd.company_id = c.id
where
    c.id = 61
group by
    c.id

The result looks like this:

61  [{"in31" : "0.0"}, {"in32" : "145.8"}, {"in34" : "134.0"}]

How do I modify the query above to return all key pair values within the same JSON object (rather then a list of jsons)? Expected output:

61  {"in31" : "0.0", "in32" : "145.8", "in34" : "134.0"}

I think you want json_object_agg ()

user330315
– user330315

2018-03-13 14:36:17 +00:00
Commented Mar 13, 2018 at 14:36 — user330315
– user330315, Commented Mar 13, 2018 at 14:36

klin · Accepted Answer · 2018-03-13 14:41:10Z

3

Replace

json_agg(json_build_object(ds.statement_ref, value)) as financials

with

json_object_agg(ds.statement_ref, value) as financials

answered Mar 13, 2018 at 14:41

klin

123k15 gold badges241 silver badges263 bronze badges

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

1 Comment

Denys Over a year ago

Thanks. Got lost in all the new functions Postgres introduced for jsons.

Collectives™ on Stack Overflow

Return a json of multiple key-value pairs instead of list of single-paired key-value jsons

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related