如何在MySQL中按json_object排序

Question

我有一个问题需要对JSON_OBJECT进行排序：

如何按照JSON OBJECT中的对象顺序进行排序？

- Tuyeyas

---

你的意思是想控制json_arrayagg()创建的数组中json对象的顺序吗？ - GMB

是的，JSON_ARRAYAGG的顺序相同。 - Tuyeyas

我也会考虑你是否真的需要一个视图。 - Strawberry

Answer 1

即使在MySQL 8.0中，json_arrayagg()生成的数组元素顺序也无法控制，如文档中所述：

将结果集聚合成一个由行组成的单个JSON数组。该数组中元素的顺序未定义。

一个丑陋而不可扩展的解决方法是使用group_concat()手动生成json数组：

select 
    dashboard,
    widget,
    ...
    concat(
        '[',
        group_concat(
            json_object('color_mode', color_mode, ...)
            order by <your_ordering_clumn>
        ),
        ']'
    ) js_array
from datadog_wigets_markers 
group by dashboard, widget, ...

长的JSON字符串会导致该方法失败，我宁愿尝试使用json_arrayagg()和无序数组来解决。

另外注意：在group by子句中应列举所有未聚合的列；这是大多数数据库的要求，也是很好的编程实践。

- GMB

---

这个有变化吗？json_arrayagg 的文档提到了一个“over clause”，但是我无法让它起作用。链接 - Bergi

我曾认为over子句可以包含order子句...但显然这部分不起作用，是的。 - Bergi

@Bergi：它有一个order by子句，适用于窗口本身，而不是被聚合的值... - GMB

@Macoshark：group_concat()在生成的字符串上有一个默认限制，最多1000个字符（当然，您可以手动增加）。此外，这种选项可能比内置的效率低（这是两个函数调用而不是一个）。唉，目前这是唯一可用的选项。遗憾的是，MySQL没有提供这样的排序选项，而JSON规范确实指定了数组中元素的顺序是有意义的。 - GMB

谢谢你的回复。只是想补充一下，如果需要的话，你可以使用(cast concat(... ) as json)将其转换回json类型。 - undefined

Answer 2

@Gordon Linoff的答案在这个帖子对于我在MySQL 8.0上有效，但我没有测试它的可伸缩性。

正如@Simas Jonelinuas的评论所述，此处是该帖子的答案（以防链接失效）。

SELECT A, json_arrayagg(json_obj('X',value1, 'Y',value2)) AS RESULT
FROM (SELECT . . .,
             ROW_NUMBER() OVER (ORDER BY value2) as seqnum
      FROM . . . 
      . . . 
     ) x
GROUP BY A;

引用：“ROW_NUMBER（）-显然-成功地对结果集进行排序，即使ORDER BY无法正常工作。”

- BloodhunterD

---

虽然这个链接可能回答了问题，但最好在此处包含答案的基本部分并提供参考链接。如果链接页面更改，仅有链接的答案可能会失效。-【来自审查】 - Simas Joneliunas

那很奇怪，但它起作用了。 - Lucio Mollinedo

Answer 3

作为解决办法，改为：

select json_arrayagg(...) from someTable order by ...

你可以：

select json_arrayagg(...) from (select someFields from someTable order by ...) as t1

- ktretyak

---

不行。那个不起作用。那就是我最初的想法。 BloodhunterD的答案有效。 - Lucio Mollinedo