By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS article_tag;
DROP TABLE IF EXISTS article_author;
DROP TABLE IF EXISTS author;
DROP TABLE IF EXISTS tag;
DROP TABLE IF EXISTS article;
CREATE TABLE IF NOT EXISTS author (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS article (
id INT(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(255),
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS tag (
id INT(11) NOT NULL AUTO_INCREMENT,
tag VARCHAR(255),
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS article_author (
article_id INT(11) NOT NULL,
author_id INT(11) NOT NULL,
PRIMARY KEY (article_id, author_id),
INDEX fk_article_author_article_idx (article_id ASC) VISIBLE,
INDEX fk_article_author_author_idx (author_id ASC) VISIBLE,
CONSTRAINT fk_article_author_article
FOREIGN KEY (article_id)
REFERENCES article (id),
CONSTRAINT fk_article_author_author
FOREIGN KEY (author_id)
REFERENCES author (id)
INSERT INTO article (id, title) VALUES (1, 'first article'), (2, 'second article'), (3, 'third article');
INSERT INTO author (id, name) VALUES (1, 'first author'), (2, 'second author'), (3, 'third author'), (4, 'fourth author');
INSERT INTO tag (id, tag) VALUES (1, 'first tag'), (2, 'second tag'), (3, 'third tag'), (4, 'fourth tag'), (5, 'fifth tag');
INSERT INTO article_tag (article_id, tag_id) VALUES (1, 1), (1, 2), (1, 3), (2, 2), (2, 4), (2, 5), (3, 1), (3, 2);
INSERT INTO article_author (article_id, author_id) VALUES (1, 1), (1, 2), (1, 3), (2, 2), (2, 4), (3, 1), (3, 2), (3, 3), (3, 4);
Records: 3 Duplicates: 0 Warnings: 0
Records: 4 Duplicates: 0 Warnings: 0
Records: 5 Duplicates: 0 Warnings: 0
Records: 8 Duplicates: 0 Warnings: 0
Records: 9 Duplicates: 0 Warnings: 0
SELECT a.id, a.title,
(SELECT JSON_ARRAYAGG(aa.author_id)
FROM article_author aa
WHERE a.id = aa.article_id
) as authors,
(SELECT JSON_ARRAYAGG(art.tag_id)
FROM article_tag art
WHERE a.id = art.article_id
) as tags
FROM article a
| id | title | authors | tags |
|---|---|---|---|
| 1 | first article | [1, 2, 3] | [1, 2, 3] |
| 2 | second article | [2, 4] | [2, 4, 5] |
| 3 | third article | [1, 2, 3, 4] | [1, 2] |
SELECT a.id, a.title,
(SELECT JSON_ARRAYAGG(JSON_OBJECT('name', au.name, 'id', au.id))
FROM article_author aa JOIN
author au
ON au.id = aa.author_id
WHERE a.id = aa.article_id
) as authors,
(SELECT JSON_ARRAYAGG(JSON_OBJECT('tag', t.tag, 'id', t.id))
FROM article_tag art JOIN
tag t
ON art.tag_id = t.id
WHERE a.id = art.article_id
) as tags
FROM article a;
| id | title | authors | tags |
|---|---|---|---|
| 1 | first article | [{"id": 1, "name": "first author"}, {"id": 2, "name": "second author"}, {"id": 3, "name": "third author"}] | [{"id": 1, "tag": "first tag"}, {"id": 2, "tag": "second tag"}, {"id": 3, "tag": "third tag"}] |
| 2 | second article | [{"id": 2, "name": "second author"}, {"id": 4, "name": "fourth author"}] | [{"id": 2, "tag": "second tag"}, {"id": 4, "tag": "fourth tag"}, {"id": 5, "tag": "fifth tag"}] |
| 3 | third article | [{"id": 1, "name": "first author"}, {"id": 2, "name": "second author"}, {"id": 3, "name": "third author"}, {"id": 4, "name": "fourth author"}] | [{"id": 1, "tag": "first tag"}, {"id": 2, "tag": "second tag"}] |