guo Matthew guo Matthew - 2 months ago 6
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

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

Comments