title: How to control the JSON_ARRAYAGG elements order in the resulting JSON string
body: <p>I am testing MySQL <code>JSON_ARRAY</code> and <code>JSON_ARRAYAGG</code> functions in order to move some application expensive processes to SQL stored functions. </p>
  
  <p>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.</p>
  
  <p>I have a <code>cart</code> table holding the general cart information</p>
  
  <pre><code>create table user_cart (
    cart_id  integer unsigned primary key,
    user_id  integer unsigned not null
  );
  </code></pre>
  
  <p>and a <code>cart_item</code> detail table,</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>Inserted the following data into the tables</p>
  
  <pre><code>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, 10551, 'ROB-10551', 12, 2.52, 0.21, 14.52),
  (30201, 20, 14313, 'PRT-14313', 11.78, 2.4738, 0.21, 14.2538),
  (30201, 10, 12211, 'ROB-12211', 78.05, 16.3905, 0.21, 94.4405),
  (30201, 1,  12821, 'COM-12821', 28.02, 5.8842, 0.21, 33.9042),
  (30201, 5,  13975, 'DEV-13975', 0.68, 0.1428, 0.21, 0.8228),
  (30201, 10, 12662, 'COM-12662', 99.95, 20.9895, 0.21, 120.9395),
  (30201, 1,  11590, 'DEV-11590', 100.25, 21.0525, 0.21, 121.3025),
  (30201, 1,  13844, 'KIT-13844', 1.12, 0.2352, 0.21, 1.3552),
  (30201, 1,  14063, 'DEV-14063', 1.9, 0.399, 0.21, 2.299),
  (30201, 3,  14280, 'WRL-14280', 1.67, 0.3507, 0.21, 2.0207),
  (30201, 1,  10391, 'ROB-10391', 3.45, 0.7245, 0.21, 4.1745),
  (30201, 1,  11021, 'DEV-11021', 4, 0.84, 0.21, 4.84),
  (30201, 2,  11884, 'ROB-11884', 18.2, 3.822, 0.21, 22.022);
  </code></pre>
  
  <p>Then, I tried to retrieve the cart items of user <code>30201</code> I wrote the following select sentence</p>
  
  <pre><code>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
  </code></pre>
  
  <p>that returns</p>
  
  <pre><code>*************************** 1. row ***************************
  json_arrayagg(jobj): [[10551, 10, "ROB-10551", 12.0, 2.52, 0.21, 14.52], [14313, 20, "PRT-14313", 11.78, 2.4738, 0.21, 14.2538], [12211, 10, "ROB-12211", 78.05, 16.3905, 0.21, 94.4405], [12821, 1, "COM-12821", 28.02, 5.8842, 0.21, 33.9042], [13975, 5, "DEV-13975", 0.68, 0.1428, 0.21, 0.8228], [12662, 10, "COM-12662", 99.95, 20.9895, 0.21, 120.9395], [11590, 1, "DEV-11590", 100.25, 21.0525, 0.21, 121.3025], [13844, 1, "KIT-13844", 1.12, 0.2352, 0.21, 1.3552], [14063, 1, "DEV-14063", 1.9, 0.399, 0.21, 2.299], [14280, 3, "WRL-14280", 1.67, 0.3507, 0.21, 2.0207], [10391, 1, "ROB-10391", 3.45, 0.7245, 0.21, 4.1745], [11021, 1, "DEV-11021", 4.0, 0.84, 0.21, 4.84], [11884, 2, "ROB-11884", 18.2, 3.822, 0.21, 22.022]]
  1 row in set (0,00 sec)
  </code></pre>
  
  <p>Scanning the result it can be easily seen that product_ids appear in the following order: 10551, 14313, 12211, 12821, 13975, 12662,..., that is the real order followed to insert the items in the database.</p>
  
  <p>But, when I try to retrieve the items in a different order, let's say ordered by <code>product_id</code>, I get the same result as before as if the <code>order by</code> clause were ignored.</p>
  
  <pre><code>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
  </code></pre>
  
  <pre><code>*************************** 1. row ***************************
  json_arrayagg(jobj): [[10551, 10, "ROB-10551", 12.0, 2.52, 0.21, 14.52], [14313, 20, "PRT-14313", 11.78, 2.4738, 0.21, 14.2538], [12211, 10, "ROB-12211", 78.05, 16.3905, 0.21, 94.4405], [12821, 1, "COM-12821", 28.02, 5.8842, 0.21, 33.9042], [13975, 5, "DEV-13975", 0.68, 0.1428, 0.21, 0.8228], [12662, 10, "COM-12662", 99.95, 20.9895, 0.21, 120.9395], [11590, 1, "DEV-11590", 100.25, 21.0525, 0.21, 121.3025], [13844, 1, "KIT-13844", 1.12, 0.2352, 0.21, 1.3552], [14063, 1, "DEV-14063", 1.9, 0.399, 0.21, 2.299], [14280, 3, "WRL-14280", 1.67, 0.3507, 0.21, 2.0207], [10391, 1, "ROB-10391", 3.45, 0.7245, 0.21, 4.1745], [11021, 1, "DEV-11021", 4.0, 0.84, 0.21, 4.84], [11884, 2, "ROB-11884", 18.2, 3.822, 0.21, 22.022]]
  1 row in set (0,00 sec)
  </code></pre>
  
  <p>Where is it expected to put the <code>order by</code> clause to take effect?
  Is there any modifier to <code>JSON_ARRAYAGG</code> as it appears to be in Oracle DB?</p>