MySQL: Return JSON from a standard SQL Query

Question

I have read about JSON objects and the JSON object type. I only want to do a select and it return JSON. I do not necessarily want to store a JSON object. Serialization per se it not my question. The columns are regular Varchar, Int, etc. columns, no JSON Objects, "normal" database rows.

Can I do a regular old SELECT and return JSON for MySQL?

Isn't this what FOR JSON in SQL SERVER and rows_for_json do in PostgreSQL?

They seemed ahead in this are but I didn't want to fool myself.

I found this question from 2016: https://stackoverflow.com/questions/35324795/mysql-5-7-return-row-as-json-using-new-json-features

Answer 1

Took a bit of figuring out (more used to PostgreSQL where things are much easier!), but if you consult the fine manual here, under 12.16.2 Functions That Create JSON Values, there's the JSON_ARRAY function, but it's not much use really - at least in this case!

To answer the question "select and it return JSON", there are two ways of doing this, both rather painful!

You can either

• use a "hack" - see the db-fiddle here,

• or use one of the new MySQL supplied JSON functions here - which, ironically, appears to be even more of a hack than the hack itself! Only with MySQL! :-) (fiddle here).

Both answers use the MySQL GROUP_CONCAT function - this post helped. You might want to set the group_concat_max_len system variable to a bit more than its default (a paltry 1024)!

The first query is, as you can imagine, messy (DDL and DML at the bottom of this answer):

SELECT CONCAT('[', better_result, ']') AS best_result FROM
(
SELECT GROUP_CONCAT('{', my_json, '}' SEPARATOR ',') AS better_result FROM
(
  SELECT 
    CONCAT
    (
      '"name_field":'   , '"', name_field   , '"', ',' 
      '"address_field":', '"', address_field, '"', ','
      '"contact_age":'  , contact_age
    ) AS my_json
  FROM contact
) AS more_json
) AS yet_more_json;

Result:

[{"name_field":"Mary","address_field":"address one","contact_age":25},{"name_field":"Fred","address_field":"address two","contact_age":35},{"name_field":"Bill","address_field":"address three","contact_age":47}]

which is correct, but, let's face it, a bit of a nightmare!

Then there's the MySQL JSON_ARRAY() approach (which is even messier - thanks MySQL for your (non-existent) implementation of the TRANSLATE() function!).

SELECT
CONCAT
('[', REPLACE
  (
    REPLACE
    (
      GROUP_CONCAT
      (
        JSON_ARRAY
        (
          'name_field:', name_field, 
          'address_field:', address_field, 
          'age_field:', contact_age
        ) SEPARATOR ','
      ), '[', '{'
    ), ']', '}'
  ), ']'
) 
AS best_result2 
FROM contact

Same result!

====  TABLE CREATION and INSERT DDL and DML ============

    CREATE TABLE contact
    (
         name_field VARCHAR  (5) NOT NULL,
      address_field VARCHAR (20) NOT NULL,
      contact_age   INTEGER      NOT NULL
    );

    INSERT INTO contact
    VALUES
    ('Mary', 'address one',   25),
    ('Fred', 'address two',   35),
    ('Bill', 'address three', 47);

Answer 2

Converting a row to json

It doesn't sound to me like you want to aggregate JSON. You state you want the equivalent of row_to_json, if so then I suggest checking out the much simpler JSON_OBJECT

SELECT JSON_OBJECT(
  'name_field', name_field,
  'address_field', address_field,
  'contact_age', contact_age
)
FROM contact;

Aggregating JSON

As a side note, if you do need to aggregate a resultset to json. then the upcoming MySQL 8 will do that for you.

• JSON_ARRAYAGG() Return result set as a single JSON array
• JSON_OBJECTAGG()` Return result set as a single JSON object

Answer 3

For an array of JSON objects (one object per row in query), you can do this:

SELECT JSON_ARRAYAGG(JSON_OBJECT("fieldA", fieldA, "fieldB", fieldB)) 
  FROM table;

It would result in a single JSON array containing all entries:

[
  {
    "fieldA": "value",
    "fieldB": "value"
  },
  ...
]

Unfortunately, MySQL does not allow for selecting all fields with *. This would be awesome, but does not work:

SELECT JSON_ARRAYAGG(JSON_OBJECT(*)) FROM table;

Answer 4

This will give you the same answer, but a lot easier code. I just used json_object with group_concat to simplify the other answer.

select 
    concat('[',
        GROUP_CONCAT(
            JSON_OBJECT(    
                'name_field', name_field
                ,'address_field', address_field
                ,'contact_age', contact_age
                )
            SEPARATOR ',')
    ,']')
    from contact;

Answer 5

If you're not looking to do this in the context of any particular host environment (e.g. node.js, Python, MySQL Workbench), then MySQL Shell supports JSON output. Each row in a result is output as a separate object. It can pretty-print output, and can output an array of objects or separate each row with newlines. For example, from the shell prompt, type:

\option resultFormat json/array

Then run a query as normal.

SELECT * FROM contacts;

As a bonus, MySQL Shell also supports JavaScript and Python, though you can only get away from SQL statements if the server is set up as a document store. Then, once you've set the active schema, you can access relational tables via the db object. Whether using JS or Python, the statement corresponding to the above SQL query is the same:

\use people
db.contacts.find()

Answer 6

Seconding @outis's answer. Using the mysql shell is the most straight forward solution to return rows as JSON from a sql formatted query.

You can specify the --result-format and the --sql parameters on the shell invocation to specify the result format as json and to use sql to interface with the server, like so:

mysqlsh -u {user} --result-format=json --sql

Then run whatever sql queries you desire and the result output will be in JSON format. Here's an example creating a JSON test db, table, insertion, and select:

     SQL > create database jsonTest;
     SQL > use jsonTest;
     SQL > create table JsonTest (field1 int default -1, field2 varchar(32) default '');
     SQL > insert into JsonTest (field1, field2) values (1, "My JSON test");
     SQL > select * from JsonTest;
      {
       "field1": 1,
       "field2": "My JSON test"
      }
      1 row in set (0.0032 sec)