MySQL One-to-Many to JSON format

Question

I have two MySQL tables:

User (id, name)
Sale (id, user, item)

Where Sale(user) is a foreign key to User(id), so this is a one-to-many relationship (one user can make many sales).

I'm trying to get this from the database and return it in JSON format for multiple users, so it would look like this:

[
  {
    "id": 1,
    "name": "User 1",
    "sales": [
      {
        "id": 1,
        "item": "t-shirt"
      },
      {
        "id": 2,
        "item": "jeans"
      }
    ]
  },
  {
    "id": 2,
    "name": "User 2",
    "sales": [
      {
        "id": 3,
        "item": "sweatpants"
      },
      {
        "id": 4,
        "item": "gloves"
      }
    ]
  }
]

Where the "sales" entities are nested within the entity of their corresponding "user".

So the question is, what is the best way to query this from the DB and turn it into JSON? I could run a query for all Users, then iterate through each one and run a query to get their sales, but this is quite slow. Or, I could do an outer join between Users and Sales and then parse that in code into the JSON format, but this sends excess information from the DB (includes the entire set of User data for each Sale) and requires looping through it all in code. Is there a convenient way to do this? I'm using Python 3.7, by the way.

Answer 1

Here is a SQL query that might meet your requirement.It uses MySQL JSON_ARRAYAGG() aggregate function to generate an array of JSON objects (which are created using JSON_OBJECT()).

An intermediate level of grouping is performed within the join, to generate the sales JSON array of each user. Then the results are aggregated into a single line, with one column that contains the resulting JSON array of objects.

SELECT
  JSON_ARRAYAGG(JSON_OBJECT('id', u.id, 'name', u.name, 'sales', s.sales))
FROM
    user u
    LEFT JOIN (
        SELECT 
            user, 
            JSON_ARRAYAGG(JSON_OBJECT('id', id, 'item', item)) sales 
        FROM sale 
        GROUP BY user
    ) s ON s.user = u.id

Demo on DB Fiddle

If you wrap the return value with JSON_PRETTY, the output is as follows :

[
  {
    "id": 1,
    "name": "User 1",
    "sales": [
      {
        "id": 1,
        "item": "t-shirt"
      },
      {
        "id": 2,
        "item": "jeans"
      }
    ]
  },
  {
    "id": 2,
    "name": "User 2",
    "sales": [
      {
        "id": 3,
        "item": "sweatpants"
      },
      {
        "id": 4,
        "item": "gloves"
      }
    ]
  }
]

---

Edit : here is an (ugly) solution for MySQL < 5.7, where JSON support is not available. It relies only on string manipulation functions. Please note that this will only work as long as the varchar fields do not contain the " character :

SELECT
    CONCAT(
        '[', 
        GROUP_CONCAT( CONCAT( '{ "id":', u.id, ', "name":"', u.name, '", "sales":', s.sales, ' }' )  SEPARATOR ', ' ),
        ']'
    )
FROM 
    user u
    LEFT JOIN (
        SELECT 
            user, 
            CONCAT( 
               '[', 
                GROUP_CONCAT( CONCAT( '{ "id":', id, ', "item":"', item, '" }' ) SEPARATOR ', '),
                ']'
            ) sales 
    FROM sale
    GROUP BY user ) s ON s.user = u.id

Demo on DB Fiddle