How would I join two tables in MySQL (many to one) and get a specific JSON output (as shown below)

Question

How would I join two tables in MySQL (many to one) and get a specific JSON output (as shown below)

Note: Please do not mark this as a duplicate

There is a previous question posted that is very similar to this, however it is not the same on several points.

this is NOT what I am looking for, and this question is not the same as this: Mysql concat and group_concat

I put a longer description with more details at the bottom of this post and why this should not be marked as a duplicate, and how it is different.

---

Requirements:

1. return JSON
2. use: JSON_OBJECT & JSON_ARRAYAGG (and NOT use CONCAT or GROUP_CONCAT)
3. joining two tables only (by a primary/foreign key | row identifier) | many to one relationship
4. display in the output, fields from the parent table (not just the id foreign key reference
5. in the ouput JSON, I need control of the name of each element or struct, which corresponds to a table name, but JSON stylized will be different, and also control of the name of each field (the JSON stylized name will be different from the database field name)

The output JSON will be an array (teams), and each element of the array (struct) will contain an array (persons).

The top level array pertains to the parent table 'teams' (each element of the JSON array containing single row data), and the nested array pertains to the child table 'persons' which has a many to one relationship to the parent.

---

If a simple select is done from one table only: select * from <child_table>;

I get the id (primary key) of the parent table, but in the JSON results, along with the parent primary key, I also want to display another another field from the parent (field values in the parent, instead of only the row id / key, which is not helpful in the JSON output).

---

Provided here: .sql files to create db, schema, insert data (to test the solution)

create schema if not exists test;

use test;
create table if not exists team
(
    id          int unsigned auto_increment primary key,
    team_name   varchar(30) unique not null,
    description text
);

use test;
create table if not exists person
(
    id          int unsigned auto_increment primary key,
    id_team     int unsigned not null,
    person_name varchar(40)  not null,
    notes       varchar(40)  not null,
    constraint team_person unique (id, id_team),
    foreign key (id_team) references team (id)
);

-- insert teams (2)
use test;
insert into team(team_name)
values ('team1');

insert into team(team_name)
values ('team2');

insert into team(team_name)
values ('team3');

-- insert persons (x2 per team = 4)
use test;
insert into person(id_team, person_name)
values ((select id from team where team_name = 'team1'),
        'john');

insert into person(id_team, person_name)
values ((select id from team where team_name = 'team1'),
        'tom');

insert into person(id_team, person_name)
values ((select id from team where team_name = 'team1'),
        'marie');

-- team 2
insert into person(id_team, person_name)
values ((select id from team where team_name = 'team2'),
        'scott');

insert into person(id_team, person_name)
values ((select id from team where team_name = 'team2'),
        'mark');

---

I am needing the output JSON to look like this (fully expanded - two arrays with values from both tables):

What you are looking at here is an array of teams, one JSON element per row in the 'team' table.

And within each of those elements, an array of persons, one JSON element per row from the 'person' table representing the many to one relationship.

{
  "team_persons": [
    {
      "team": "team1",
      "id_team": 1,
      "persons": [
        {
          "id_person": 1,
          "personaName": "john"
        },
        {
          "id_person": 2,
          "personaName": "allison"
        }
      ]
    },
    {
      "team": "team2",
      "id_team": 2,
      "persons": [
        {
          "id_person": 3,
          "personaName": "katrina"
        },
        {
          "id_person": 4,
          "personaName": "scott"
        }
      ]
    }
  ]
}

As you can see in the output, the parent table fields: id_team & team_name are both displayed in each record in the JSON output.

There are two arrays in the JSON:

1. A top level array (of structs) that contains the list or collection of teams (parent table)
2. And within each team, there is a list or collection of persons that belong to the team

I know that SELECT JSON_OBJECT / JSON_ARRAYAGG would be involved. I am not sure how to use both in a SQL join of two tables only to achieve the desired customized JSON output.

A join of two tables in SQL would look like this:

select t.id id_team, p.id id_person, p.person_name, t.team_name
from team t,
     person p
where t.id = p.id_team;

thank you!

---

Please do not mark this as a duplicate, it is not:

I am dealing with only two tables where the JSON output needs to start with a very top level array (unlike the other post).

{
  "team_persons": [

There are problems with the referenced post and the approach:

1. the example in the referenced post does not provide adequate details on the tables and relationships, furthermore it is an actual problem and not broken down to it's simplest essential elements
2. The initial approach attempts to use CONCAT / GRUOP_CONCAT, which I want to avoid using

There is a better, more modern way to do this with other built-in MySQL functions.

Please do not close this question. The requirements are very different.

I need an approach using: JSON_OBJECT / JSON_ARRAYAGG and NOT CONCAT and NOT GROUP_CONCAT. And also on two very simple tables only.

When asking a question, if the simplest (not exact) thing trying to be solved helps others map their problem onto the question. In the question below, I have removed all complexity to provide a simple example.

I am hoping to provide both a question that is simpler to follow, where others may benefit (with code to duplicate the table structure), and a more refined answer as I believe the answer given there could be made simpler for a simpler use case and easier to understand.

Answer 1

SELECT 
  JSON_PRETTY(
    JSON_OBJECT(
      'team_persons', JSON_ARRAYAGG(
        JSON_OBJECT(
          'team', team,
          'id_team', id_team,
          'persons', persons
        )
      )
    )
  ) AS _result
FROM (
  SELECT 
    t.id AS id_team,
    t.team_name AS team,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'id_person', p.id, 
        'personName', p.person_name
      )
    ) AS persons
  FROM team t JOIN person p ON t.id = p.id_team
  GROUP BY id_team
) AS p;

Tested on MySQL 8.0.28, but it should also work on MySQL 5.7.22 (or later).

---

To include empty teams is tricky. At first one can just use LEFT OUTER JOIN, but then it complains unless I change the GROUP BY to reference the base column, not the alias:

  ...
  FROM team t LEFT OUTER JOIN person p ON t.id = p.id_team
  GROUP BY t.id

However, this probably doesn't result in what you want, which I assume is an empty JSON array. Because the result of an outer join does have a row, but with NULLs, you get one fake team member with NULLs:

{
  "team": "team3",
  "id_team": 3,
  "persons": [
    {
      "id_person": null,
      "personName": null
    }
  ]
}

One workaround is to UNION the current query with the JOIN with another exclusion-join query:

SELECT
  JSON_PRETTY(
    JSON_OBJECT(
      'team_persons', JSON_ARRAYAGG(
        JSON_OBJECT(
          'team', team,
          'id_team', id_team,
          'persons', persons
        )
      )
    )
  ) AS _result
FROM (
  SELECT 
    t.id AS id_team,
    t.team_name AS team,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'id_person', p.id,
        'personName', p.person_name
      )
    ) AS persons
  FROM team t JOIN person p ON t.id = p.id_team
  GROUP BY t.id
  UNION
  SELECT 
    t.id,
    t.team_name,
    JSON_ARRAY()
  FROM team t LEFT OUTER JOIN person p ON t.id = p.id_team
  WHERE p.id_team IS NULL
) AS p;

The latter part of the UNION returns a literal empty array, because there are no people on the team anyway.