guo Matthew guo Matthew - 11 months ago 45
JSON Question

how to select keys and values from mysql database to a json format

mysql5.7 has supported the json data type. Many questions and office documents show how to save a key/value as a json format into mysql database.

My question is how to transform a key/value to a json format when doing the select directly?
For example:

There is a description for a table as below :

desc ttlsa_users\G;

*************************** 1. row ***************************

Field: uid
Type: int(11) unsigned
Null: NO
Key: PRI
Default: NULL

*************************** 2. row ***************************

Field: username
Type: varchar(40)
Null: NO
Default: NULL


and there is a record in the table:


I want to get a json format like

How can I select from the table so that the database keys can be json keys and database values can be json values?

The reason I want to do this is I need to select the data from mysql database and transfer them to a json format in my program.

And one more question, if the data can be transferred, how about the performance since the mysql needs to do the transformation?

Answer Source


JSON_OBJECT([key, val[, key, val] ...])
Evaluates a (possibly empty) list of key/value pairs and returns a JSON object containing those pairs. An error occurs if any key name is NULL or the number of arguments is odd.

So your query is

SELECT JSON_OBJECT('uid',uid,'username',username) FROM ttlsa_users

As for performances, unless you are fetching thousands of records at once it doesn't matter whether you do it here or in the client side