Mysql output a column from other tables

August 7, 2023

This is my schema

CREATE TABLE notes(
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(800),
  markdown TEXT(65535),
  PRIMARY KEY(id)
);

CREATE TABLE tags(
  id INT NOT NULL AUTO_INCREMENT,
  label VARCHAR(800),
  PRIMARY KEY(id)
);

CREATE TABLE noteTags(
  id INT NOT NULL AUTO_INCREMENT,
  noteId INT,
  tagId INT,
  PRIMARY KEY(id)
);

Notes has a many to many relationship to tags where each note is unique and each tag is unique as well. Im wondering if I can do a select query where the output would look something like this in JSON.

[
    {
        "id": 1,
        "title": "Markdown CheatSheets",
        "markdown": "### this is a markdown",
        "tagIds": [1, 3]
    }
]

>Solution :

Yes, you can achieve the desired JSON output using a SELECT query with JOIN and GROUP_CONCAT in MySQL. The GROUP_CONCAT function will allow you to concatenate the related tag IDs into a comma-separated string, which can then be parsed and converted to an array in your application code. Here’s the SQL query to achieve the JSON output:

MySQL Query:

SELECT
    n.id,
    n.title,
    n.markdown,
    CONCAT('[', GROUP_CONCAT(t.id SEPARATOR ','), ']') AS tagIds
FROM
    notes n
JOIN
    noteTags nt ON n.id = nt.noteId
JOIN
    tags t ON nt.tagId = t.id
GROUP BY
    n.id, n.title, n.markdown;

Output:

[{
    "id": 1,
    "title": "Markdown CheatSheets",
    "markdown": "### this is a markdown",
    "tagIds": "[1,3]"
}, {
    "id": 2,
    "title": "Another Note",
    "markdown": "### Some content",
    "tagIds": "[2,3]"
}]