MYSQL JSON ordering by values-CodePudding

I am using a JSON type to store some data in a table, and I'm having some trouble ordering said data while I'm fetching it.

Example Data:

{"574161434674462720":1,"870013663581437952":6,"903739315782320168":3,"913739315950071829":1}

The json here is being ordered by it's key while its stored which is fine, but when I try to fetch it with say "SELECT json -> '$.*' FROM table" it keeps the same order.

output: ('[1, 6, 3, 1]',)

I've tried some funky stuff like:

"SELECT json -> '$.*' FROM table ORDER BY CAST(json -> '$.*' AS UNSIGNED) ASC"

But the output is still the same.

I'm hoping to be able to get an output something like:

["870013663581437952":6, "903739315782320168":3, "574161434674462720":1, "913739315950071829":1]

or something along those lines.

CodePudding user response：

If you want to sort the values in JSON array then you must parse the array to single elements then reconstruct it with needed ordering.

SELECT JSON_ARRAYAGG(jsontable.value) OVER (ORDER BY jsontable.value) sorted
FROM test
CROSS JOIN JSON_TABLE(test.jsondata -> '$.*',
                      '$[*]' COLUMNS (value INT PATH '$')) jsontable
ORDER BY sorted DESC LIMIT 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=120495fc6f53829d83681d6f3ff574d4