Search code examples
mysqljsonmysql-json

MYSQL JSON ordering by values

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.

Solution

  • 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