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

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

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

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;

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;