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?