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
Extra:

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

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

=============================================================


and there is a record in the table:

uid:1
username:"Jim";


I want to get a json format like
{"uid":"1","username":"jim}
.

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

use JSON_OBJECT

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