chocomuesli chocomuesli - 2 months ago 7
JSON Question

Build JSON from 2 aggregated columns in Postgres

using a Postgres-Db as source for json-documents, I need to convert two columns from a table to an JSON-object.

So I have the columns "color_id", "language" and "name" in a table of colors:


color_id | language | name
1 | "de" | "blau"
1 | "en" | "blue"
1 | "fr" | "bleu"


And I'd like to produce a JSON-object like:


{
"de": "blau",
"fr": "bleu",
"en": "blue"
}


I started with


SELECT
array_to_json(array_agg((language::text, name::text))),
color_id
FROM colors
GROUP BY color_id;


which unfortunately produced


array to json | color_id
"[{"f1":"de","f2":"blau"} |
, {"f1":"en","f2":"blue"} | 1
, {"f1":"fr","f2":"bleu"}]" |


I'd think it would be simple - more or less -, but found myself at a dead end of misleading results and syntax errors.

Kind regards, Dominik

Answer

Use jsonb_object_agg():

with data(color_id, language, name) as (
values
    (1, 'de', 'blau'),
    (1, 'en', 'blue'),
    (1, 'fr', 'bleu')
)
select color_id, jsonb_object_agg(language, name)
from data
group by 1;

 color_id |              jsonb_object_agg              
----------+--------------------------------------------
        1 | {"de": "blau", "en": "blue", "fr": "bleu"}
(1 row)