How to control how JSON_ARRAYAGG orders results

Question

I am testing MySQL JSON_ARRAY and JSON_ARRAYAGG functions in order to move some application expensive processes to SQL stored functions.

In the following e-commerce application example, I am trying to retrieve a user's cart, not as a resultset of rows from the cart_items table, but as a JSON string containing all the needed information for a Javascript call back function to render the cart.

I have a cart table holding the general cart information

create table user_cart (
  cart_id  integer unsigned primary key,
  user_id  integer unsigned not null,
);

and a cart_item detail table,

create table cart_item (
  cart_item_id  integer unsigned primary key auto_increment,
  cart_id       integer unsigned not null,
  product_id    integer unsigned not null,

  qty           integer unsigned not null,
  ref           varchar(15) not null,

  -- total
  base          double default 0.0,
  tax           double default 0.0,
  tax_pct       double default 0.0,
  total         double default 0.0,

  foreign key (cart_id) references user_cart(cart_id),
);

Inserted the following data into the tables

insert into user_cart(user_id, cart_id) values
(9001, 30201),
(9001, 30202);
(9001, 30203),
(9001, 30245),
(9001, 30300),
(9001, 30344);

insert into cart_item(cart_id, qty, product_id, ref, base, tax, tax_pct, total) values
(30201, 10, 90000, 'BBB-90000', 12, 2.52, 0.21, 14.52),
(30201, 20, 33333, 'JKL-33333', 11.78, 2.4738, 0.21, 14.2538),
(30201, 10, 12211, 'BBB-12211', 78.05, 16.3905, 0.21, 94.4405),
(30201, 1,  12821, 'XXX-12821', 28.02, 5.8842, 0.21, 33.9042),
(30201, 5,  10000, 'DLO-10000', 0.68, 0.1428, 0.21, 0.8228),
(30201, 10, 12345, 'XXX-12345', 99.95, 20.9895, 0.21, 120.9395),
(30201, 1,  11590, 'DLO-11590', 100.25, 21.0525, 0.21, 121.3025),
(30201, 1,  45000, 'NKN-45000', 1.12, 0.2352, 0.21, 1.3552),
(30201, 1,  14999, 'DLO-14999', 1.9, 0.399, 0.21, 2.299),
(30201, 3,  98700, 'XYI-98700', 1.67, 0.3507, 0.21, 2.0207),
(30201, 1,  10391, 'BBB-10391', 3.45, 0.7245, 0.21, 4.1745),
(30201, 1,  11021, 'DLO-11021', 4, 0.84, 0.21, 4.84),
(30201, 2,  11884, 'BBB-11884', 18.2, 3.822, 0.21, 22.022);

Then, I tried to retrieve the cart items of user 30201 I wrote the following select sentence

select json_arrayagg(jobj) from (
  select json_array(product_id, qty, ref, base, tax, tax_pct, total) as jobj
  from cart_item where cart_id = 30201
) as jarray\G

that returns

*************************** 1. row ***************************
json_arrayagg(jobj): [[90000, 10, "BBB-90000", 12.0, 2.52, 0.21, 14.52], [33333, 20, "JKL-33333", 11.78, 2.4738, 0.21, 14.2538], [12211, 10, "BBB-12211", 78.05, 16.3905, 0.21, 94.4405], [12821, 1, "XXX-12821", 28.02, 5.8842, 0.21, 33.9042], [10000, 5, "DLO-10000", 0.68, 0.1428, 0.21, 0.8228], [12345, 10, "XXX-12345", 99.95, 20.9895, 0.21, 120.9395], [11590, 1, "DLO-11590", 100.25, 21.0525, 0.21, 121.3025], [45000, 1, "NKN-45000", 1.12, 0.2352, 0.21, 1.3552], [14999, 1, "DLO-14999", 1.9, 0.399, 0.21, 2.299], [98700, 3, "XYI-98700", 1.67, 0.3507, 0.21, 2.0207], [10391, 1, "BBB-10391", 3.45, 0.7245, 0.21, 4.1745], [11021, 1, "DLO-11021", 4.0, 0.84, 0.21, 4.84], [11884, 2, "BBB-11884", 18.2, 3.822, 0.21, 22.022]]
1 row in set (0,00 sec)

Scanning the result it can be easily seen that product_ids appear in the following order: 90000, 33333, 12211, 12821, 10000, 12345,..., that is the real order followed to insert the items in the database.

But, when I try to retrieve the items in a different order, let's say ordered by product_id, I get the same result as before as if the order by clause were ignored.

select json_arrayagg(jobj) from (   
  select json_array(product_id, qty, ref, base, tax, tax_pct, total) as jobj   
  from cart_item where cart_id = 30201 order by product_id 
) as jarray\G

*************************** 1. row ***************************
json_arrayagg(jobj): [[90000, 10, "BBB-90000", 12.0, 2.52, 0.21, 14.52], [33333, 20, "JKL-33333", 11.78, 2.4738, 0.21, 14.2538], [12211, 10, "BBB-12211", 78.05, 16.3905, 0.21, 94.4405], [12821, 1, "XXX-12821", 28.02, 5.8842, 0.21, 33.9042], [10000, 5, "DLO-10000", 0.68, 0.1428, 0.21, 0.8228], [12345, 10, "XXX-12345", 99.95, 20.9895, 0.21, 120.9395], [11590, 1, "DLO-11590", 100.25, 21.0525, 0.21, 121.3025], [45000, 1, "NKN-45000", 1.12, 0.2352, 0.21, 1.3552], [14999, 1, "DLO-14999", 1.9, 0.399, 0.21, 2.299], [98700, 3, "XYI-98700", 1.67, 0.3507, 0.21, 2.0207], [10391, 1, "BBB-10391", 3.45, 0.7245, 0.21, 4.1745], [11021, 1, "DLO-11021", 4.0, 0.84, 0.21, 4.84], [11884, 2, "BBB-11884", 18.2, 3.822, 0.21, 22.022]]
1 row in set (0,00 sec)

Where is it expected to put the order by clause to take effect? Is there any modifier to JSON_ARRAYAGG as it appears to be in Oracle DB?

Answer 1

This is a fun one, if you've got a hard functional requirement to do the ordering in the database. Then this works, you can use MySQL's "feature" against itself by first inserting the data into a temporary table in the order you want it.

drop temporary table if exists temp_results;
create temporary table temp_results
select
    json_array(product_id, qty, ref, base, tax, tax_pct, total) as jobj
from cart_item
where cart_id = 30201
    order by
    product_id;

Then you can do the json_arrayagg func back out and it's in the order you want.

select
    json_arrayagg(jobj)
from temp_results tr;