Convert table to JSON array with longtext column

Question

I'm using mariaDB 10.3, I have a table:

CREATE TABLE user(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, parameters longtext,  PRIMARY KEY(id));

With rows:

INSERT INTO user VALUES (1, 'name1', '{"number": 1, "text": "some text"}'), (2, 'name2', '{"number": 2, "text": "some more text"}');

I'm trying to write query that returns the table as JSON object. So far I have

SELECT CONCAT(
    '[',
      GROUP_CONCAT(JSON_OBJECT('id',id,'name',name,'parameters', parameters)),
    ']'
 ) 
FROM user;

But this returns:

[
  {"id": 1,
    "name": "name1",
    "parameters": "{\"number\": 1, \"text\": \"some text\"}"
  },
  {
    "id": 2,
    "name": "name2",
    "parameters": "{\"number\": 2, \"text\": \"some more text\"}"
  }
]

which is not a proper JSON. What should I change to get parameters properly formatted?

What I would like to get is:

[
  {
    "id": 1,
    "name": "name1",
    "parameters": {
      "number": 1,
      "text": "some text"
    }
  },
  {
    "id": 2,
    "name": "name2",
    "parameters": {
      "number": 2,
      "text": "some more text"
    }
  }
]

Thanks

Answer 1

Just JSON_COMPACT function, which's proper to MariaDB and does not exists in MySQL, might be applied for the parameters column

SELECT CONCAT(
       '[',
        GROUP_CONCAT(JSON_OBJECT('id',id,
                                 'name',name,'parameters', 
                                  JSON_COMPACT(parameters))),
       ']'
       ) AS "JSON Value"
  FROM user

Demo

Answer 2

You can use JSON_OBJECT , JSON_ARRAYAGG(expr) concept to get your result. ref: https://www.tutorialspoint.com/mysql/mysql_aggregate_functions_json_arraygg.htm

I used this for one of my project work