sql - 将表转换为带有长文本列的 JSON 数组

Question

我正在使用 mariaDB 10.3，我有一个表：

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

有行：

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

我正在尝试编写将表作为JSON对象返回的查询。到目前为止我有

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

但这会返回：

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

这不是一个正确的 JSON。我应该改变什么以获得parameters正确的格式？

我想得到的是：

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

谢谢

标签： sqljsonmariadbmariadb-10.3

Answer 1

仅JSON_COMPACT适用于 MariaDB 且在 MySQL 中不存在的函数可以应用于该parameters列

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

Demo