MySQL 8.0

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)
);

CREATE TABLE IF NOT EXISTS article_tag (
  article_id INT(11) NOT NULL,
  tag_id INT(11) NOT NULL,
  PRIMARY KEY (article_id, tag_id),
  INDEX fk_article_tag_article_idx (article_id ASC) VISIBLE,
  INDEX fk_article_tag_tag_idx (tag_id ASC) VISIBLE,
  CONSTRAINT fk_article_tag_article
    FOREIGN KEY (article_id)
    REFERENCES article (id),
  CONSTRAINT fk_article_tag_tag
    FOREIGN KEY (tag_id)
    REFERENCES tag (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"}]

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]

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"}]