Fetching constraints in SQL database to JSON in PHP

Let’s assume I have the following database structure of car manufacturers and the corresponding cars:

manufacturers:

----------------------------------
| manufacturer | founded_in | id |
|--------------|------------|----|
| Daimler AG   | 1927       | 1  |
| Volkswagen AG| 1937       | 2  |
----------------------------------

cars:

-------------------------------------
| car     | built_in | manufacturer |
|---------|----------|---------------
| C Class | 1993     | 1            |
| E Class | 1993     | 1            |
| Golf    | 1974     | 2            |
-------------------------------------

The id column of manufacturers is the primary key and the manufacturer column of cars has the corresponding foreign key constraint.

I’d like to produce the following JSON output using PHPs json_encode:

{
    "manufacturers": {
        "Daimler AG": {
            "founded in": "1927",
            "cars": [
                "C Class",
                "E Class"
            ]
        },
        "Volkswagen AG": {
            "founded in": "1937",
            "cars": [
                "Golf"
            ]
        }
    }
}

To get the manufacturers and their founded_in I’d just perform:

SELECT manufacturer, founded_in FROM manufacturers

And fetch them to an array. How do I assign the cars correctly though, after making a second query?

Answer

If you are using MySQL 5.7 or higher, you can use the JSON aggregate functions to generate the output you expect directly from a SQL query. I expect that will be more efficient than using php in between.

To start with, consider the following aggregates query, that JOINs both tables and creates a JSON object for each manufacturer, with the list of associated car names in a subarray:

SELECT JSON_OBJECT('founded in', m.founded_in, 'cars', JSON_ARRAYAGG(c.car)) js
FROM manufacturers m
INNER JOIN cars c ON c.manufacturer = m.id
GROUP BY m.id, m.founded_in;

This returns:

| js                                                   |
| ---------------------------------------------------- |
| {"cars": ["C Class", "E Class"], "founded in": 1927} |
| {"cars": ["Golf"], "founded in": 1937}               |

To produce the expected output, we can turn this to a subquery and add another level of aggregation:

SELECT JSON_OBJECT('manufacturers', JSON_OBJECTAGG(manufacturer, js)) myjson
FROM (
    SELECT 
        m.id, 
        m.manufacturer, 
        JSON_OBJECT('founded in', m.founded_in, 'cars', JSON_ARRAYAGG(c.car)) js
    FROM manufacturers m
    INNER JOIN cars c ON c.manufacturer = m.id
    GROUP BY m.id, m.manufacturer, m.founded_in
) x

This yields:

| myjson                                                                                                                                           |
| ------------------------------------------------------------------------------------------------------------------------------------------------ |
| {"manufacturers": {"Daimler AG": {"cars": ["C Class", "E Class"], "founded in": 1927}, "Volkswagen AG": {"cars": ["Golf"], "founded in": 1937}}} |

Demo on DB Fiddle.