0

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.

Saratoubi
  • 3
  • 2
  • You can't cast an array to a number. – Barmar Nov 03 '21 at 15:13
  • Which element of the array do you want to order by? – Barmar Nov 03 '21 at 15:14
  • 2
    `ORDER BY` orders rows, not items in a string or JSON document on a given row. – Bill Karwin Nov 03 '21 at 15:16
  • 1
    You may be able to use `JSON_TABLE()` to split the object into separate rows of a table. Then you can order that. – Barmar Nov 03 '21 at 15:19
  • I found this article earlier https://jianjye.com/articles/32/how-to-sort-mysql-json-data-correctly , but I can't seem to get it to work with what I've got here. I'm assuming is because my JSON is badly organized or something. – Saratoubi Nov 03 '21 at 15:29
  • Please [edit] your question to show us the results you want from the sample data you showed us. – O. Jones Nov 03 '21 at 15:41
  • *output: ('[1, 6, 3, 1]',)* This is **ONE** scalar value, its datatype is JSON, and the value is JSON array. ORDER BY by single value, even when it is JSON array, makes no sense. – Akina Nov 03 '21 at 17:03

1 Answers1

0

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

Akina
  • 39,301
  • 5
  • 14
  • 25
  • Thank you very much, is there a way to also include the keys with the sorted values? – Saratoubi Nov 03 '21 at 19:37
  • @Saratoubi Of course. Add one more JSON_TABLE and parse JSON_KEYS output. For correct joining add FOR ORDINALITY column. – Akina Nov 04 '21 at 06:28