How to convert result table to JSON array in MySQL

by

New solution:

Built using Your great comments, thanks!

SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'phone', phone)) from Person;

Old solution:

With help from @Schwern I managed to put up this query, which seems to work!

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

More Related Contents:

  1. MySQL – Rows to Columns
  2. How to search JSON data in MySQL?
  3. Storing Data in MySQL as JSON
  4. How do I generate nested json objects using mysql native json functions?
  5. MySQL JSON: How to find object from key value
  6. Using MySQL JSON field to join on a table
  7. How can write queries in MySQL that can parse JSON data in a column?
  8. Convert JSON array in MySQL to rows
  9. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  10. How to retrieve JSON data from MySQL?
  11. How to update JSON data type column in MySQL 5.7.10?
  12. How to create and insert a JSON object using MySQL queries?
  13. MySQL search json value by key in array
  14. MySQL vs. JSON – Why? [closed]
  15. 1064 error in CREATE TABLE … TYPE=MYISAM
  16. LOAD DATA INFILE easily convert YYYYMMDD to YYYY-MM-DD?
  17. When to use SELECT … FOR UPDATE?
  18. BULK INSERT in MYSQL
  19. Setting column values as column names in the SQL query result
  20. Count table rows
  21. MYSQL sum() for distinct rows
  22. How to lock a single row
  23. MySQL Cannot drop index needed in a foreign key constraint
  24. Laravel join with 3 Tables
  25. MySQL Insert & Joins
  26. How to join two tables using a comma-separated-list in the join field
  27. How to regex in a MySQL query
  28. Syntax error on MERGE statement
  29. Get column sum and use to calculate percent of total (mySQL)
  30. is there a length limit to group_concat or another reason why it would not work on a text field

Leave a Comment