How to control how JSON_ARRAYAGG orders results

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, 'B

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;

StackExchange Database Administrators Q#242916, answer score: 2