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!

Answer 1

Use jsonb_build_array() and json_object_agg().

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

DbFiddle.