Converting MySQL Result Array to Json

How to convert result table to JSON array in MySQL

New solution:

Built using Your great comments, thanks!

SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'phone', phone)) from Person;

---

Old solution:

With help from @Schwern I managed to put up this query, which seems to work!

SELECT CONCAT(
    '[', 
    GROUP_CONCAT(JSON_OBJECT('name', name, 'phone', phone)),
    ']'
) 
FROM person;

JSON encode MySQL results

$sth = mysqli_query($conn, "SELECT ...");
$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);

The function json_encode needs PHP >= 5.2 and the php-json package - as mentioned here

NOTE: mysql is deprecated as of PHP 5.5.0, use mysqli extension instead http://php.net/manual/en/migration55.deprecated.php.

Transforming a MySQL result set into a single JSON OBJECT (MySQL datatype)

One option is to use JSON_MERGE()(deprecated 5.7.22) --> JSON_MERGE_PATCH() / JSON_MERGE_PRESERVE(), see 12.16.1 JSON Function Reference.

SET @`json_LathamCR_CreditRequest` :=
  (SELECT
     CONCAT(
       '[',
       GROUP_CONCAT(
         JSON_OBJECT('requestId', `requestId`)
       ),
       ']')
  FROM `LathamCR_CreditRequest`);

SET @`json_LathamCR_RMARequest` :=
  (SELECT
     CONCAT(
       '[',
       GROUP_CONCAT(
         JSON_OBJECT('requestId', `requestId`)
       ),
       ']')
  FROM `LathamCR_RMARequest`);

SELECT
  `der`.`result`,
  JSON_VALID(`der`.`result`) `JSON_VALID?`
FROM (
  SELECT
    JSON_OBJECT(
      'results',
      JSON_MERGE(@`json_LathamCR_CreditRequest`,
                 @`json_LathamCR_RMARequest`
      )
  ) `result`
) `der`;

See db-fiddle.

Python converting mysql query result to json

You can use cursor description to extract row headers:
row_headers=[x[0] for x in cursor.description] after the execute statement. Then you can zip it with the result of sql to produce json data.
So your code will be something like:

from flask import Flask
from flask.ext.mysqldb import MySQL
import json
app = Flask(__name__)
app.config['MYSQL_HOST'] = '127.0.0.1'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'password'
app.config['MYSQL_DB'] = 'hello_db'
mysql = MySQL(app)

@app.route('/hello')
def index():
   cur = mysql.connection.cursor()
   cur.execute('''SELECT * FROM Users WHERE id=1''')
   row_headers=[x[0] for x in cur.description] #this will extract row headers
   rv = cur.fetchall()
   json_data=[]
   for result in rv:
        json_data.append(dict(zip(row_headers,result)))
   return json.dumps(json_data)

if __name__ == '__main__':
   app.run(debug=True)

In the return statement you can use jsonify instead of json.dumps as suggested by RickLan in the comments.

How to convert result of mysql query in json format in PHP?

You need to change this below two lines as follows,

$json = json_encode($rows); // to convert it into json internal array
//$json = json_encode($rows,JSON_FORCE_OBJECT); // to convert it json object
fwrite($handle, "json: $json \n\n");

This should do the trick.

How to convert JSON object rows into JSON array in MySQL?

You can use MySQL JSON_ARRAYAGG aggregation function:

SELECT JSON_ARRAY_AGG(JSON_OBJECT('uniqueId', uniqueId, 'name', name)) actors
FROM (
select stf.id as familyId, stl.skill_type_name  as name
from actor_family af, actor_layered al
where af.id = al.actor_family_id) AS actors
GROUP BY uniqueId;

Try it here.

---

Related Topics