How Can I Convert MySQL Result Tables into JSON Arrays?

Querying MySQL for JSON Output

Converting result tables into JSON arrays is a useful capability for data manipulation and exchange. In MySQL, this can be achieved through a combination of built-in functions and operators.

Simple Query for a Single JSON Array

To create a JSON array from a result table, the JSON_ARRAYAGG function can be employed. This function aggregates values from a specified column and returns a JSON array.

For instance, to convert the following result table:

| name | phone |
| Jack | 12345 |
| John | 23455 |

into a JSON array using plain MySQL commands, the following query can be used:

SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'phone', phone))
FROM person;

This query will produce the following JSON array:

[
  {
    "name": "Jack",
    "phone": 12345
  },
  {
    "name": "John",
    "phone": 23455
  }
]

Customizing the JSON Array

To modify the structure or contents of the JSON array, additional operators and functions can be employed. For example, the GROUP_CONCAT function can be used to concatenate multiple rows into a single string.

Consider the following query as an alternative solution:

SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('name', name, 'phone', phone)), ']')
FROM person;

This query will generate the same JSON array as the previous example. However, it allows for more flexibility in customizing the structure of the array, such as including additional elements or formatting options.

Conclusion

These MySQL commands provide a straightforward and efficient way to convert result tables into JSON arrays. Whether you require a simple array structure or customized output, there are options available to cater to your specific needs without the use of external languages or tools.

The above is the detailed content of How Can I Convert MySQL Result Tables into JSON Arrays?. For more information, please follow other related articles on the PHP Chinese website!