build JSON object with dynamic keys and json values

Question

I am attempting to build a JSON object with a postgres query. The output I'm looking for is similar to the object below. The properties "xxx" and "yyy" come from a column as do the dates.

{
    "xxx": [ "2018-07-26T11:42:04.514Z", "2018-07-26T11:52:04.514Z"],
    "yyy": [ "2018-07-26T05:42:09.210Z", "2018-07-26T07:22:04.024Z"]
}

I was hoping to do this with a query similar to the one below:

SELECT
    json_object(
        array_agg(name),
        array_agg(json_build_array(start_date, end_date)
    )
FROM my_table

The my_table table would look roughly like this:

name | start_date                | end_date                 |
-------------------------------------------------------------
xxx  | 2018-07-26T11:42:04.514Z  | 2018-07-26T11:52:04.514Z |
yyy  | 2018-07-26T05:42:09.210Z  | 2018-07-26T07:22:04.024Z |

However, json_object only accepts text arrays and I can't seem to find an alternative. So, I get ERROR: function json_object(text[], json[]) does not exist. Thanks for reading!

klin · Accepted Answer · 2018-07-31 22:17:55Z

10

Use jsonb_build_array() and json_object_agg().

select json_object_agg(name, jsonb_build_array(start_date, end_date))
from my_table

DbFiddle.

answered Jul 31, 2018 at 22:17

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

build JSON object with dynamic keys and json values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related