MySQL: Return JSON from a standard SQL Query

jsonMySQL

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

Best Answer

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);
Related Question