javascript - 我想要两个 mysql 表与 group_concat 连接导致 json 数组

Question

我想要两个 mysql 表与 group_concat 连接结果以嵌套的 json 数组格式，我的表名称为 posts[post_id, user_id, description, created_date] 和 files[post_id, saved_name]一切都很好，但只有 file_name 值不是数组格式

输出

"文件名":"3.jpg, 2.jpg"

要求的输出

"file_name":["1.jpg", "2.jpg", "3.jpg"] json 数组中的值我正在尝试使用 JSON_OBJECT 的 mysql，但出现错误

SELECT JSON_OBJECT('post_id', T1.post_id, 'user_id', T1.user_id,
                   JSON_ARRAYAGG(
        JSON_OBJECT('post_id', T2.post_id, 'saved_name', T2.saved_name,
GROUP_CONCAT(T2.saved_name ORDER BY  T2.post_id ASC)))) AS 'file_Name' 
FROM posts AS T1 INNER JOIN files AS T2 ON T1.post_id = T2.post_id   
group by T1.post_id ORDER BY created_date DESC

1582 - 调用 native 函数“JSON_OBJECT”时的参数计数不正确

  #Current Out PUT  
    {
        "status": 200,
        "error": null,
        "res_posts": [{
            "post_id": 3,
            "user_id": 1,
            "description": " Working a Fine ",
            "post_type": 0,
            "created_date": "2019-01-25T18:40:41.000Z",
            "saved_name": "8.jpg",
            "file_Name": "7.jpg,8.jpg"
        }, {
            "post_id": 2,
            "user_id": 1,
            "description": " Hello hi",
            "post_type": 1,
            "created_date": "2019-01-21T12:51:16.000Z",
            "saved_name": "4.jpg",
            "file_Name": "6.jpg,5.jpg,4.jpg"
        }, {
            "post_id": 1,
            "user_id": 1,
            "description": " Hi How are you ",
            "post_type": 0,
            "created_date": "2019-01-21T12:50:51.000Z",
            "saved_name": "1.jpg",
            "file_Name": "3.jpg,2.jpg,1.jpg"
        }]
    }





 #Required OUT PUT:


{
    "status": 200,
    "error": null,
    "res_posts": [{
        "post_id": 3,
        "user_id": 1,
        "description": " Working a Fine ",
        "post_type": 0,
        "created_date": "2019-01-25T18:40:41.000Z",
        "saved_name": "8.jpg",
        "file_Name": ["7.jpg", "8.jpg"]
    }, {
        "post_id": 2,
        "user_id": 1,
        "description": " Hello hi",
        "post_type": 1,
        "created_date": "2019-01-21T12:51:16.000Z",
        "saved_name": "4.jpg",
        "file_Name": ["6.jpg","5.jpg","4.jpg"]
    }, {
        "post_id": 1,
        "user_id": 1,
        "description": " Hi How are you ",
        "post_type": 0,
        "created_date": "2019-01-21T12:50:51.000Z",
        "saved_name": "1.jpg",
        "file_Name": ["3.jpg","2.jpg","1.jpg"]
    }]
}

我当前的查询:

SELECT  T1.*, T2.post_id, T2.saved_name, 
GROUP_CONCAT(T2.saved_name ORDER BY T2.post_id ASC) AS 'file_Name' 
FROM posts AS T1 INNER JOIN files AS T2 ON T1.post_id = T2.post_id   
group by T1.post_id ORDER BY created_date DESC

必填查询

SELECT JSON_OBJECT('post_id', T1.post_id, 'user_id', T1.user_id,
                   JSON_ARRAYAGG(
        JSON_OBJECT('post_id', T2.post_id, 'saved_name', T2.saved_name,
GROUP_CONCAT(T2.saved_name ORDER BY  T2.post_id ASC)))) AS 'file_Name' 
FROM posts AS T1 INNER JOIN files AS T2 ON T1.post_id = T2.post_id   
group by T1.post_id ORDER BY created_date DESC

1582 - 调用 native 函数“JSON_OBJECT”时的参数计数不正确

Answer 1

改变:

JSON_OBJECT(
  'post_id', T2.post_id,
  'saved_name', T2.saved_name,
  GROUP_CONCAT(T2.saved_name ORDER BY  T2.post_id ASC)
) AS 'file_Name'

通过

JSON_OBJECT(
  'post_id', T2.post_id,
  'saved_name', MAX(T2.saved_name),
  'file_Name', GROUP_CONCAT(T2.saved_name ORDER BY  T2.post_id ASC)
)

参见 dbfiddle .

更新

类似于:

[{"7.jpg"}, {"6.jpg"}, {"8.jpg"}]

不被视为有效的 JSON。

尝试:

JSON_OBJECT(
  'post_id', `post_id`,
  'saved_name', MAX(`saved_name`),
  'file_Name', JSON_ARRAYAGG(CONCAT('{', `saved_name`, '}'))
) `JSON`

参见 dbfiddle .