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

I tested your functions in MySQL 8.2. They both work as you intend, creating a JSON object with a nested JSON object.

So you must be using MariaDB, not MySQL. I tested the function in MariaDB 11.2, and I can reproduce the unwanted behavior you describe, the JSON variable is converted to a string.

Please be aware that MariaDB and MySQL are effectively different products. In particular, they both implemented JSON features after MariaDB forked, so they behave differently. MySQL implemented a full-blown data type for JSON, but MariaDB basically implemented it as little more than an alias for the TEXT type with a few extra functions.

I was going to suggest using JSON_OBJECT('obj', CAST(s AS JSON)) which is a way to convert a string to JSON if the string contains value JSON syntax. That works in MySQL.

But MariaDB doesn't implement CAST(... AS JSON).

I found this workaround:

CopyCREATE FUNCTION sf_textjson()
RETURNs json
BEGIN
declare s JSON;
SET s = JSON_OBJECT('year', 2024);
RETURN JSON_OBJECT('obj', JSON_EXTRACT(s, '$'));
END 

I tested this on MariaDB 11.2, and it works as you intend:

Copymysql> SELECT VERSION();
+---------------------------------------+
| VERSION()                             |
+---------------------------------------+
| 11.2.2-MariaDB-1:11.2.2+maria~ubu2204 |
+---------------------------------------+

mysql> SELECT JSON_PRETTY( sf_textjson() ) AS _result;
+--------------------------------------------------+
| _result                                          |
+--------------------------------------------------+
| {
    "obj": 
    {
        "year": 2024
    }
} |
+--------------------------------------------------+

MariaDB is not MySQL. We should all stop thinking of it as a "drop-in replacement for MySQL," no matter what their marketing claims. It already has numerous features that are incompatible, and this list is growing larger over time.