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

Question

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?

e4c5 · Accepted Answer · 2017-08-11 00:27:23Z

4

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 performance, unless you are fetching tens of thousands of records at once it doesn't matter whether you do it here or in the client side. If this is a web application, in all likely hood you will only be fetching a handful of records. So performance will primarily be determined by how efficient your query is whether it uses indexes etc.

edited Aug 11, 2017 at 0:27

answered Sep 28, 2016 at 10:35

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

guo Matthew Over a year ago

Thanks. How about the performace by mysql to do the json transformation insteand of the program?

e4c5 Over a year ago

Unless you are fetching thousands of records at once it doesn't matter whether you do it here or in the client side

Akshay Tilekar · Accepted Answer · 2016-09-28 10:39:16Z

0

in php the code is as:

$return_arr = array();
    $fetch = mysql_query("SELECT * FROM ttlsa_users\G"); 

    while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
        $row_array['uid'] = $row['uid'];
        $row_array['username'] = $row['username'];

        array_push($return_arr,$row_array);
    }

    echo json_encode($return_arr);

answered Sep 28, 2016 at 10:39

Akshay Tilekar

2,0502 gold badges14 silver badges23 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related