6

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 '19 at 17:08
  • Well, it follows a "natural order", the one I commented before: the order of growing cart_item_id. Jul 15 '19 at 17:09
  • What I want to do is change that order by any other more convenient. Jul 15 '19 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 '19 at 17:12
  • 1
    @Akina thanks for the detailed answer. I am using group_concat() and struggling with its size limitations Jul 16 '19 at 10:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.