MySQL with Node: how to return nested data

Question

I have three tables: post, tag and post_tag.

CREATE TABLE post (
  id INT NOT NULL,
  title VARCHAR(45) NULL,
  PRIMARY KEY (id));

CREATE TABLE tag (
  id INT NOT NULL,
  tag VARCHAR(45) NULL,
  PRIMARY KEY (id));

CREATE TABLE post_tag (
  post_id INT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  INDEX fk_post_tag_tag1_idx (tag_id ASC),
  INDEX fk_post_tag_post_idx (post_id ASC),
  CONSTRAINT fk_post_tag_post
    FOREIGN KEY (post_id)
    REFERENCES post (id),
  CONSTRAINT fk_post_tag_tag1
    FOREIGN KEY (tag_id)
    REFERENCES tag (id));

INSERT INTO post (id, title) VALUES (1, 'post 1');
INSERT INTO post (id, title) VALUES (2, 'post 2');
INSERT INTO tag (id, tag) VALUES (1, 'tag 1');
INSERT INTO tag (id, tag) VALUES (2, 'tag 2');
INSERT INTO post_tag (post_id, tag_id) VALUES (1, 1);
INSERT INTO post_tag (post_id, tag_id) VALUES (1, 2);
INSERT INTO post_tag (post_id, tag_id) VALUES (2, 1);
INSERT INTO post_tag (post_id, tag_id) VALUES (2, 2);

Then I can create a stored procedure to retrieve the first post with its tags:

DELIMITER $$
CREATE PROCEDURE select_posts_tags(IN id INT)
BEGIN
  SELECT *
  FROM post
    INNER JOIN post_tag pt ON post.id = pt.post_id
    INNER JOIN tag t ON t.id = pt.tag_id
      WHERE post.id = id
      GROUP BY post.id, t.id;
END $$
DELIMITER ;

And finally I call the stored procedure from Node:

var mysql = require("mysql");
var connection = mysql.createConnection({
  host: "127.0.0.1",
  user: "test_database",
  password: "test_database",
  database: "test_database",
});

connection.connect();

const sql = `CALL select_posts_tags(${1})`;

connection.query(sql, (error, results) =>
  console.log(JSON.stringify(results[0], null, 4))
);

connection.end();

But the result is an array of flat objects:

[
    {
        "id": 1,
        "title": "post 1",
        "post_id": 1,
        "tag_id": 1,
        "tag": "tag 1"
    },
    {
        "id": 2,
        "title": "post 1",
        "post_id": 1,
        "tag_id": 2,
        "tag": "tag 2"
    }
]

The result is an array of the same flat JSON object repeated twice with different tags;

How can I retrieve the tags as a nested array within «tags» key within the post objects? The result should be this:

[
  {
    id: 1,
    title: "post 1",
    tags: [
      {
        id: 1,
        tag: "tag 1",
      },
      {
        id: 2,
        tag: "tag 2",
      },
    ],
  },
];

Thanks!

GMB · Accepted Answer · 2020-05-19 21:20:08Z

1

You could use json_arrayagg() and json_object() as follows:

SELECT 
    p.id, 
    p.title,
    json_arrayagg(json_object('id', t.id, 'tag', t.tag)) tags
FROM post p
INNER JOIN post_tag pt ON p.id = pt.post_id
INNER JOIN tag t ON t.id = pt.tag_id
WHERE p.id = ?
GROUP BY p.id, p.title;

answered May 19, 2020 at 21:20

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Emille C. Over a year ago

Thanks, makes sense… but I'm getting escaped JSON within the tags array: "[{\"id\": 1, \"tag\": \"tag 1\"}, {\"id\": 2, \"tag\": \"tag 2\"}]". Is there a way to avoid that?

Emille C. Over a year ago

The error with the nested JSON returned as string was due to use npm mysql package instead of mysql2

Collectives™ on Stack Overflow

MySQL with Node: how to return nested data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related