use-JSON_OBJECT-as-GROUP_CONCAT

use-JSON_OBJECT-as-GROUP_CONCAT.sql

#Schema:
CREATE TABLE posts(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY , title VARCHAR(500) NOT NULL , body TEXT NOT NULL);
CREATE TABLE keywords(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(500) NOT NULL);
CREATE TABLE post_keyword(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, post_id int NOT NULL, keyword_id INT NOT NULL );


#Test Data:
INSERT INTO posts VALUES (NULL, 'post-title-A', 'This Content Of Post A'), (NULL , 'post-title-B', 'This Content Of Post B'), (NULL , 'post-title-C', 'This Content Of Post C');
INSERT INTO keywords VALUES (NULL, 'keyword-A'), (NULL, 'keyword-B'), (NULL, 'keyword-C');
INSERT INTO post_keyword VALUES (NULL, 1,1), (NULL, 1,2), (NULL, 1,3), (NULL, 2,2), (NULL, 3,1);


#Retirve Data Query:
SELECT
  p.id,
  p.title,
  p.body,
    CONCAT('[', GROUP_CONCAT((JSON_OBJECT('id', k.id, 'title', k.title))), ']') AS keywords
FROM posts p
  LEFT JOIN post_keyword pk
    ON pk.post_id = p.id
  LEFT JOIN keywords k
    ON k.id = pk.keyword_id
GROUP BY
  p.id,
  p.title,
  p.body;


#Resultset:
id  title         body                    KeywordsJSON
1	  post-title-A	This Content Of Post A	[{"id": 1, "title": "keyword-A"},{"id": 2, "title": "keyword-B"},{"id": 3, "title": "keyword-C"}]
2	  post-title-B	This Content Of Post B	[{"id": 2, "title": "keyword-B"}]
3	  post-title-C	This Content Of Post C	[{"id": 1, "title": "keyword-A"}]