How to convert Result set into Json in MYSQL

Question

select id,name,age from person where id=1;

This query gives result like below

id | name | age
1  |manoj | 20

I want a JSON like below

"{"id":1,"name":"manoj","age":5}"

I want a dynamic way.When I try another query from another table ,that result as like previous JSON

I want to generate JSON from a table and store into a column in MYSQL, I don't want to use php or other server side language for generate this JSON.

How can I get JSON in MYSQL ?

Answer 1

Use the JSON_OBJECT() function:

SELECT JSON_OBJECT('id', id, 'name', name, 'age', age)
FROM person
WHERE id = 1;

This requires at least MySQL 5.7 or MariaDB 10.2.3, that's when all the JSON-related functions were added.

If you don't want to hard-code the column names into the query, you'll need to write a stored procedure that creates dynamic SQL, using INFORMATION_SCHEMA.COLUMNS to get all the column names.