CODEWITHSUNDEEP
sqlpostgresql
khex
khex

Reputation: 2828

How to UNNEST, GROUP and JOIN an array?

I'm trying to UNNEST cars.colors array, then GROUP or group all colors in array and then join it. But it's so hur with it's nested SELECTs.

This is SQLFiddle with example.

create table cars (
    cars_id integer,
    name    char(32),
    colors  int []
);

insert into cars (cars_id, name, colors) values
    (1, 'liftback',  array [1, 2, 3]),
    (2, 'hatchback', array [2, 4, 6]),
    (3, 'sedan',     array [3, 4, 6]),
    (4, 'phaeton',   array [4, 5, 6]);

create table tags (
    tags_id  integer,
    shade    char(16)
);

insert into tags (tags_id, shade) values
    (1, 'green'), (2, 'blue'),  (3, 'yellow'), (4, 'black'), (5, 'white'), (6, 'red');

But how to insert array_agg inside unnest?

SELECT
    cars.name,
    array_agg(tags.shade) AS shade_colors
FROM cars
LEFT JOIN tags ON cars.cars_id = tags.tags_id
GROUP BY cars.cars_id;

I whant to recieve smth like

enter image description here

enter image description here

Thats what I receive

enter image description here

Upvotes: 0

Views: 3204

Answers (2)

Chris McGraw
Chris McGraw

Reputation: 36

Have you tried breaking into into pieces with a with statement?

with cars_qry as (
select cars_id, name, unnest(colors) as shade
from cars
)

select c.name, array_agg(t.shade) as shade_colors
from cars_qry as c
left join tags as t
   on c.cars_id = t.tags_id
group by c.name

or you could do it within the from statement:

from (select cars_id, name, unnest(colors) as shade from cars) as c

Upvotes: 1

Gen Wan
Gen Wan

Reputation: 2009

Try this (unnest first then array_agg again):

select a.name, array_agg(t.shade ) as shade_colors
from (
select c.cars_id, c.name, unnest(c.colors) as colorid
from cars c)a
left join tags t
on a.colorid = t.tags_id
group by a.name

Test Result: SQL<>Fiddle

Upvotes: 1

Related Questions