Build JSON from 2 aggregated columns in Postgres

Question

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

klin · Accepted Answer · 2018-10-10 13:47:58Z

17

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)

The function jsonb_object_agg() was introduced in Postgres 9.5.

In Postgres 9.4 use json_object_agg() instead.

In Postgres 9.3 you have to construct the result using string functions:

select 
    color_id, 
    format('{%s}', string_agg(format('"%s": "%s"', language, name), ', '))::json
from data
group by 1;

edited Oct 10, 2018 at 13:47

answered Oct 7, 2016 at 16:55

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

wheeleruniverse Over a year ago

You saved me! I'm using a version of Postgres later than 9.5, but we want to keep the json in alphabetical order so I had to use this format('{%s}'... example as jsonb_object_agg() wasn't maintaining the Order of elements.Thanks

Nishant Bajracharya · Accepted Answer · 2018-03-30 13:23:30Z

1

For key value pair and Proper JSON data this can be used

with data(color_id, language, name) as (
values
    (1, 'de', 'blau'),
    (1, 'en', 'blue'),
    (1, 'fr', 'bleu')
)
    SELECT color_id,
     json_agg((SELECT x FROM (SELECT language, name) AS x)) AS json_data
FROM data
group by "data".color_id;

answered Mar 30, 2018 at 13:23

Nishant Bajracharya

3613 silver badges13 bronze badges

1 Comment

mlibby Over a year ago

Your output is an array containing multiple objects containing single key/value pairs. If the output needs to be a single object containing multiple key/value pairs, then using jsonb_object_agg() is a better idea.

Collectives™ on Stack Overflow

Build JSON from 2 aggregated columns in Postgres

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related