Select multiple rows into one row from 2 tables-CodePudding

I have 2 tables:

--BEAT TABLE--

CREATE TABLE beat (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  artist_name TEXT NOT NULL,
  genre TEXT
);

-- TAG TABLE --

CREATE TABLE tag (
  id BIGSERIAL PRIMARY KEY,
  beat_id BIGINT REFERENCES beat (id) NOT NULL,
  tag_name TEXT NOT NULL
);

One beat can have 2 tags for example. So, when I select beat I use next query:

SELECT
  *
FROM
  beat
  LEFT JOIN tag ON tag.beat_id = beat.id
WHERE beat.id = 1;

But this query returns me 2 rows: beat and tag with id 1 and second row beat tag with id 2.

But I want to get it as single row: beat tag with id 1 tag with id 2.

UPD: -- Example data --

INSERT INTO beat (title, artist_name, genre) VALUES ('Beat-1', 'Artist-1', 'electro;

INSERT INTO tag (beat_id, tag_name) VALUES (1, 'Tag-1');

INSERT INTO tag (beat_id, tag_name) VALUES (1, 'Tag-2');

-- Expected Output --

id 1
title Beat-1
artist_name Artist-1
genre electro
id 1
beat_id 1
tag_name Tag-1
id 2
beat_id 1
tag_name Tag-2

CodePudding user response：

SELECT
  *  
FROM
  beat b
left join lateral (
    select json_agg(json_build_object('id',t.id, 'tag',t.tag_name)) as tags
    from tag t
    where t.beat_id = b.id
    ) c on true
WHERE b.id = 1