Ordering rows by JSON array column on MySQL & MariaDB

Question

PostgreSQL allows rows to be sorted by arrays. It compares the first value of each array, then the second value and so on (fiddle):

select array[2, 4] as "array"
union
select array[10] as "array"
union
select array[2, 3, 4] as "array"
union
select array[10, 11] as "array"
order by "array"

array
[2, 3, 4]
[2, 4]
[10]
[10, 11]

The closest equivalent on MySQL and MariaDB seem to be JSON arrays.

MySQL apparently orders arrays by length more or less randomly (fiddle):

select json_array(2, 4) as `array`
union
select json_array(10) as `array`
union
select json_array(2, 3, 4) as `array`
union
select json_array(10, 11) as `array`
order by `array`

array
[10]
[2, 4]
[10, 11]
[2, 3, 4]

MariaDB somewhat orders by value but does it incorrectly (fiddle). Integers are ordered like strings (10 before 2) and arrays with the same beginning are reversed ([10, 11] before [10]):

select json_array(2, 4) as `array`
union
select json_array(10) as `array`
union
select json_array(2, 3, 4) as `array`
union
select json_array(10, 11) as `array`
order by `array`

array
[10, 11]
[10]
[2, 3, 4]
[2, 4]

Is there the a way to replicate PostgreSQL's array ordering on MySQL and/or MariaDB?

The arrays can have any length and I don't know the maximum length.

The only workaround/hack I see at the moment is concatenating the array into a string and left-padding the values with 0s to the same length: 002.004, 010.011 etc.

Answer 1

It looks like a bug to me. According to docs

Two JSON arrays are equal if they have the same length and values in corresponding positions in the arrays are equal.

If the arrays are not equal, their order is determined by the elements in the first position where there is a difference. The array with the smaller value in that position is ordered first. If all values of the shorter array are equal to the corresponding values in the longer array, the shorter array is ordered first.

But ORDER BY looks not following such rules at all.

This is a DB fiddle for MySQL 8 & 5.7

I'm using CROSS JOIN and explicit comparison to get the expected ordering.

SELECT f.`array`, SUM(f.`array` > g.`array`) cmp
FROM jsons f
CROSS JOIN jsons g
GROUP BY f.`array`
ORDER BY cmp
;

There is another observation for MySQL 5.7, when using subquery, > is doing something like string comparison, it needs to cast to JSON again to get correct result while MySQL8 does not need to do so.

SELECT f.`array`, SUM(CAST(f.`array` AS JSON) > CAST(g.`array` AS JSON)) cmp
FROM (
 select json_array(2, 4) as `array`
 union
 select json_array(10) as `array`
 union
 select json_array(2, 3, 4) as `array`
 union
 select json_array(10, 11) as `array`
) f
CROSS JOIN (
 select json_array(2, 4) as `array`
 union
 select json_array(10) as `array`
 union
 select json_array(2, 3, 4) as `array`
 union
 select json_array(10, 11) as `array`
) g
GROUP BY f.`array`
ORDER BY cmp
;

The above does not work in MariaDB.

https://mariadb.com/kb/en/incompatibilities-and-feature-differences-between-mariadb-106-and-mysql-80/

In MySQL, JSON is compared according to json values. In MariaDB JSON strings are normal strings and compared as strings.

Query below works for MariaDB

WITH RECURSIVE jsons AS (
 select json_array(2, 4) as `array`
 union
 select json_array(10) as `array`
 union
 select json_array(2, 3, 4) as `array`
 union
 select json_array(10, 11) as `array`
),
maxlength AS (
 SELECT MAX(JSON_LENGTH(`array`)) maxlength
 FROM jsons
),
numbers AS (
 SELECT 0 AS n
 FROM maxlength
 UNION ALL
 SELECT n + 1
 FROM numbers
 JOIN maxlength ON numbers.n < maxlength.maxlength - 1
),
expanded AS (
 SELECT a.`array`, b.n, JSON_EXTRACT(a.`array`, CONCAT('$[', b.n, ']')) v
 FROM jsons a
 CROSS JOIN numbers b
),
maxpadding AS (
 SELECT MAX(LENGTH(v)) maxpadding
 FROM expanded
)
SELECT a.`array`
FROM expanded a
CROSS JOIN maxpadding b
GROUP BY a.`array`
ORDER BY GROUP_CONCAT(LPAD(a.v, b.maxpadding, '0') ORDER BY a.n ASC)

Answer 2

Using JSON_VALUE:

WITH cte AS (
  select json_array(2, 4) as `array`
  union
  select json_array(10) as `array`
  union
  select json_array(2, 3, 4) as `array`
  union
  select json_array(10, 11) as `array`
)
select *
from cte
order by CAST(JSON_VALUE(`array`, '$[0]') AS INT),
         CAST(JSON_VALUE(`array`, '$[1]') AS INT),
         CAST(JSON_VALUE(`array`, '$[2]') AS INT)
        -- ...;


-- MySQL 8.0.21+
select *
from cte
order by
 JSON_VALUE(`array`, '$[0]' RETURNING SIGNED),
 JSON_VALUE(`array`, '$[1]' RETURNING SIGNED),
 JSON_VALUE(`array`, '$[2]' RETURNING SIGNED)

db<>fiddle demo

Output: