Aggregate key column and multiple value columns to JSON object

Question

I have this table:

CREATE TABLE my_table (
    id uuid PRIMARY KEY,
    name text NOT NULL,
    key text NOT NULL,
    x int NOT NULL,
    y int NOT NULL,
    UNIQUE (name, key)
);

With this data:

id                                   name key x y
12345678-abcd-1234-abcd-123456789000 foo  a   1 2
12345678-abcd-1234-abcd-123456789001 foo  b   3 4
12345678-abcd-1234-abcd-123456789002 foo  c   5 6
12345678-abcd-1234-abcd-123456789003 foo  d   7 8
12345678-abcd-1234-abcd-123456789004 bar  v   0 0
12345678-abcd-1234-abcd-123456789005 bar  w   1 1
12345678-abcd-1234-abcd-123456789006 bar  z   2 2
12345678-abcd-1234-abcd-123456789007 baz  a   8 7
12345678-abcd-1234-abcd-123456789008 baz  b   6 5
12345678-abcd-1234-abcd-123456789009 baz  c   4 3
12345678-abcd-1234-abcd-123456789010 baz  d   2 1

I have this query:

SELECT name, json_build_object(key, json_build_object('x', x, 'y', y))
FROM my_table;

With this result:

name json_build_object
foo  {"a" : {"x" : 1, "y" : 2}}
foo  {"b" : {"x" : 3, "y" : 4}}
foo  {"c" : {"x" : 5, "y" : 6}}
foo  {"d" : {"x" : 7, "y" : 8}}
bar  {"v" : {"x" : 0, "y" : 0}}
bar  {"w" : {"x" : 1, "y" : 1}}
bar  {"z" : {"x" : 2, "y" : 2}}
baz  {"a" : {"x" : 8, "y" : 7}}
baz  {"b" : {"x" : 6, "y" : 5}}
baz  {"c" : {"x" : 4, "y" : 3}}
baz  {"d" : {"x" : 2, "y" : 1}}

What I'd like to have:

foo {"a" : {"x" : 1, "y" : 2}, "b" : {"x" : 3, "y" : 4}, "c" : {"x" : 5, "y" : 6}, "d" : {"x" : 7, "y" : 8}}
bar {"v" : {"x" : 0, "y" : 0}, "w" : {"x" : 1, "y" : 1}, "z" : {"x" : 2, "y" : 2}}
baz {"a" : {"x" : 8, "y" : 7}, "b" : {"x" : 6, "y" : 5}, "c" : {"x" : 4, "y" : 3}, "d" : {"x" : 2, "y" : 1}}

I know I need to GROUP BY name and aggregate the other columns, but couldn't find a suitable combination of functions. Is this even possible with a single query?

S-Man · Accepted Answer · 2018-12-11 15:26:27Z

5

Your query should be:

SELECT 
    name, 
    json_object_agg(key, json_build_object('x', x, 'y', y)) 
FROM data
GROUP BY name

demo:db<>fiddle

json_object_agg does exactly what you want.

answered Dec 11, 2018 at 15:26

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Aggregate key column and multiple value columns to JSON object

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related