7

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?

10
  • Technically it is unordered, correct?
    – Rick James
    Jul 15, 2019 at 17:08
  • Well, it follows a "natural order", the one I commented before: the order of growing cart_item_id. Jul 15, 2019 at 17:09
  • What I want to do is change that order by any other more convenient. Jul 15, 2019 at 17:11
  • I have manually trimmed many of the superfluous columns in the tables. In case any error occurs testing the code ask me, please Jul 15, 2019 at 17:12
  • 1
    @Akina thanks for the detailed answer. I am using group_concat() and struggling with its size limitations Jul 16, 2019 at 10:07

1 Answer 1

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.