sqljsonmariadbmariadb-10.3

Convert table to JSON array with longtext column


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


Solution

  • 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