chocomuesli chocomuesli - 11 months ago 50
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

array_to_json(array_agg((language::text, name::text))),
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 Source

Use jsonb_object_agg():

with data(color_id, language, name) as (
    (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)