How to order by array value inside json path mysql?-CodePudding

I have a column in db table which is of a JSON type.

This column will store such values:

column 1: ["bmw", "opel", "audi"] 
column 2: ["opel", "bwm", "audi"]
column 3: ["audi"]

the order of values is different as well as the total number but in general there is a list of valid values to be stored.

On UI, there is a table with columns which can be sorted. When user clicks on a column Car Brand, ASC, I will need to sort the output from db based on the column values I mentioned above.

I'd like to receive the following output:

1: ["audi"]
2: ["audi", "bmw", "opel"]
3: ["audi", "bmw", "opel"]

I can't seem to find the solution for this using JSON path.

Can anybody help me?

Please do not suggest to store the values in a different way, it's not me who decides this.

CodePudding user response：

You can't do this in MySQL.

What Akina describes in the comment above is like this demo:

set @j = '["opel", "bwm", "audi"]';

select * from json_table(@j, '$[*]' columns (a varchar(10) path '$')) j order by a;
 ------ 
| a    |
 ------ 
| audi |
| bwm  |
| opel |
 ------

Then the idea is to recombine these rows into a JSON array using JSON_ARRAYAGG(). However, this doesn't work:

select json_arrayagg(a) as j from (
  select * from json_table(@j, '$[*]' columns (a varchar(10) path '$')) j order by a
 ) as a;

 ------------------------- 
| j                       |
 ------------------------- 
| ["opel", "bwm", "audi"] |
 -------------------------

The ORDER BY in the subquery has been factored out. MySQL thinks there's no reason to sort rows in a derived table, they should be sorted as the last step of the outer query.

https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_json-arrayagg says:

JSON_ARRAYAGG(col_or_expr)

Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined.

https://bugs.mysql.com/bug.php?id=94696 is a feature request from 2019, asking for a way to sort elements before aggregating them with JSON_ARRAYAGG(). That ticket is in a "Verified" state, meaning it has been accepted as a feature request, but it has not been implemented.

There is no solution yet in MySQL 8.0 to use JSON_ARRAYAGG() to produce an array in a specific order.

So the best you can do is fetch them as sorted rows, as I showed in the intermediate step earlier in this answer, and then combine them into an array in that order in your client application code.