Manipulating Javascript Object Format

Question

I am trying to receive all of the players under a certain team along with the team's id.

Here is my query:

SELECT player.team_id, team.name, player.first_name, player.last_name FROM player, team WHERE player.team_id = team.id AND player.league = "League 1";

I am taking the result (stored in teams) and sending it to my client by doing this:

res.send({
        success: true,
        teams: teams
      });

Here is what it returns when I reach its endpoint:

I want to reformat this date to where I get one field for each unique team. It should have a players field (array) inside so I can access the names of each player part of the team. Does anyone know how I can manipulate this data to that format?

So for example, for this data set, inside the teams object, there would only be one field named "Warriors" with a players array that has all of those people's name inside of it.

I tried following the answer, but now it's just returning it as a string, not an actual object.

GMB · Accepted Answer · 2020-06-21 23:11:19Z

1

You can use aggrgation and json functions:

select
    t.team_id,
    t.name,
    json_array_agg(
        json_object('first_name', p.first_name, 'last_name', p.last_name)
    ) as players
from teams t
inner join players p on p.team_id = t.team_id
group by t.team_id, t.name

The third column in the resultset is a json array that contains json objects that represent the players, with keys first_name and last_name.

You can also aggregate the whole resulset as a json array, with another level of aggreation:

select json_array_agg(
    json_object('team_id', team_id, 'name', name, 'players', players) 
) res
from (
    select
        t.team_id,
        t.name,
        json_array_agg(
            json_object('first_name', p.first_name, 'last_name', p.last_name)
        ) as players
    from teams t
    inner join players p on p.team_id = t.team_id
    group by t.team_id, t.name
) t

Edit: json_array_agg() is available starting version 10.5.0 only. In earlier versions, an alternative is string concatenation:

select
    t.team_id,
    t.name,
    concat(
        '[',
        group_concat(json_object('first_name', p.first_name, 'last_name', p.last_name)),
        ']'
    ) as players
from teams t
inner join players p on p.team_id = t.team_id
group by t.team_id, t.name

edited Jun 21, 2020 at 23:11

answered Jun 21, 2020 at 22:17

GMB

224k25 gold badges103 silver badges151 bronze badges

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

8 Comments

legit98 Over a year ago

Getting an error. My MySQL is based on MariaDB I believe. Getting this: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.name, JSON_ARRAYAGG( JSON_OBJECT('first_name', p.first_name, 'las' at line 3

GMB Over a year ago

@legit98: there is a missing comma... fixed.

legit98 Over a year ago

Getting this now - FUNCTION db.JSON_ARRAYAGG does not exist. I tried using the function you provided as well.. I may need to update my MySQL version. Is there any other way of accomplishing the restructuring? Rather not update.

GMB Over a year ago

@legit98: which version are you running? select version() will tell you.

legit98 Over a year ago

10.4.11-MariaDB

|

Collectives™ on Stack Overflow

Manipulating Javascript Object Format

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related