Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

begin;
create temp table items(
item_id bigint primary key, name text);
create temp table tags_record_maps(
tag_id bigint,
tag_name text,
tag_color text,
target_record_id bigint  references items(item_id));
insert into items values(1,'item1'), (2,'item_2'),(3,'item3');
insert into tags_record_maps (tag_id, tag_name, tag_color, target_record_id)
values
(1, 'real','yellow',1),
(2, 'fake','purple',1),
(3, 'real_1','pink',2),
(4, 'real_2','blue',2),
(5, 'real','red',3);
commit;
3 rows affected

5 rows affected

select i.item_id, i.name, tag_id, tag_name, tag_color
from items i join tags_record_maps trm on i.item_id = trm.target_record_id;


item_id
name
tag_id
tag_name
tag_color


1
item1
1
real
yellow

1
item1
2
fake
purple

2
item_2
3
real_1
pink

2
item_2
4
real_2
blue

3
item3
5
real
red


create type tags as(tag_id bigint, tag_name text,tag_color text);
select item_id,name,  array_agg(row(trm.tag_id, tag_name, tag_color)::tags) as tags
from items i join tags_record_maps trm on i.item_id = trm.target_record_id
group by 1,2;


item_id
name
tags


1
item1
{"(1,real,yellow)","(2,fake,purple)"}

2
item_2
{"(3,real_1,pink)","(4,real_2,blue)"}

3
item3
{"(5,real,red)"}


with a as(
select item_id,name,  array_agg(row(trm.tag_id, tag_name, tag_color)::tags) as tags
from items i join tags_record_maps trm on i.item_id = trm.target_record_id
group by 1,2)
select a.item_id, a.tags[2].tag_id from a;


item_id
tag_id


1
2

2
4

3
null

item_id	name	tag_id	tag_name	tag_color
1	item1	1	real	yellow
1	item1	2	fake	purple
2	item_2	3	real_1	pink
2	item_2	4	real_2	blue
3	item3	5	real	red

item_id	name	tags
1	item1	{"(1,real,yellow)","(2,fake,purple)"}
2	item_2	{"(3,real_1,pink)","(4,real_2,blue)"}
3	item3	{"(5,real,red)"}