Given tables like this:

media

media_id | media_url | etc.

products

product_id | product_title | product_media_ids | etc.

Where product_media_ids is a JSON field with an array of IDs - e.g.: [3,6,1]

Along with other query data, I want to be able to get an array of the media_urls (e.g. ['image3.jpg', 'image6.jpg', 'image1.jpg']) in the order that the product_media_ids is in.

I can use a query like this:

SELECT
P.*,
  (SELECT JSON_ARRAYAGG(JSON_OBJECT('id', M2.media_id, 'url', M2.media_url))
  FROM media AS M2
  WHERE JSON_CONTAINS(P.product_media_ids, CAST(M2.media_id AS JSON), '$')
  ) AS image_array
FROM products AS P;

Which gives me the data I want, but not sorted according to the original data source.

Note this is a highly simplified version of the overall query and tables involved; I'd like to keep this aspect as simple as possible.

I'm using MySQL 8.4.4 - I've done a bunch of searching and haven't found a good option, but a lot of the stuff is dated. Any ideas?

Given tables like this:

media

media_id | media_url | etc.

products

product_id | product_title | product_media_ids | etc.

Where product_media_ids is a JSON field with an array of IDs - e.g.: [3,6,1]

Along with other query data, I want to be able to get an array of the media_urls (e.g. ['image3.jpg', 'image6.jpg', 'image1.jpg']) in the order that the product_media_ids is in.

I can use a query like this:

SELECT
P.*,
  (SELECT JSON_ARRAYAGG(JSON_OBJECT('id', M2.media_id, 'url', M2.media_url))
  FROM media AS M2
  WHERE JSON_CONTAINS(P.product_media_ids, CAST(M2.media_id AS JSON), '$')
  ) AS image_array
FROM products AS P;

Which gives me the data I want, but not sorted according to the original data source.

Note this is a highly simplified version of the overall query and tables involved; I'd like to keep this aspect as simple as possible.

I'm using MySQL 8.4.4 - I've done a bunch of searching and haven't found a good option, but a lot of the stuff is dated. Any ideas?

• mysql
• json

Share Improve this question Follow asked Mar 20 at 23:07 Ben in CABen in CA 8711616 silver badges2525 bronze badges 3

• I couldn't find what appeared to be a simple, maintainable, and efficient method to order these results directly with SQL. So I'm just post-sorting the JSON array based on the original field. It's only a few lines of code, and easier to understand what it's doing, and probably not dramatically different from a performance perspective. – Ben in CA Commented Mar 21 at 15:16
• If in the future MySQL supports an "ORDER BY" clause within JSON_ARRAYAGG (which it appears not to currently), then I'd consider changing to such instead. – Ben in CA Commented Mar 21 at 15:19
• 1 This was requested in 2019, but there is no news on it since then. bugs.mysql/bug.php?id=94696 – Bill Karwin Commented Mar 21 at 16:31

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

I suggest using JSON_TABLE() with ordinality column.

Then JSON_ARRAYAGG() with OVER() clause. Or GROUP_CONCAT with ORDER BY.

See example

select product_id,product_title
  ,cast(product_media_ids as json) product_media_ids
  ,cast(arr2 as json)image_array
from(
  SELECT p.*,id_ord
    ,JSON_ARRAYAGG(JSON_OBJECT('id', m.media_id, 'url', m.media_url)) 
         over(partition by product_id order by ids.id_ord) arr2
    ,max(id_ord)over(partition by product_id) id_cnt
  FROM products p
  cross join JSON_TABLE(product_media_ids, "$.medias[*]"
         COLUMNS(
           id_ord for ordinality,
           media_id INT PATH "$"
         )
       ) ids
  left  join media m on m.media_id=ids.media_id
)a
where id_ord=id_cnt

product_id	product_title	product_media_ids	image_array
1	Product-1	{"id": "100", "medias": [2, 4, 3]}	[{"id": 2, "url": "Media-2"}, {"id": 4, "url": "Media-4"}, {"id": 3, "url": "Media-3"}]
2	Product-2	{"id": "102", "medias": [6, 1]}	[{"id": 6, "url": "Media-6"}, {"id": 1, "url": "Media-1"}]

with sample data

product_id	product_title	product_media_ids
1	Product-1	{"id": "100", "medias": [2, 4, 3]}
2	Product-2	{"id": "102", "medias": [6, 1]}

media_id	media_url
1	Media-1
2	Media-2
3	Media-3
4	Media-4
5	Media-5
6	Media-6

fiddle

Update1

If you go back to your original idea,
you can assemble image_array using group_concat, which has the order by option.
Next, turn it into the json format.

select product_id,product_title
  ,cast(product_media_ids as json) product_media_ids
  ,cast( 
    (select concat('[',
           group_concat(concat('{"',m.media_id,'":"',m.media_url,'"}') order by id_ord)
       ,']') js 
     from JSON_TABLE(product_media_ids, "$.medias[*]"
         COLUMNS(
           id_ord for ordinality,
           media_id INT PATH "$"
         )
       ) ids
     left  join media m on m.media_id=ids.media_id
    ) as json)image_array
FROM products p

product_id	product_title	product_media_ids	image_array
1	Product-1	{"id": "100", "medias": [2, 4, 3]}	[{"2": "Media-2"}, {"4": "Media-4"}, {"3": "Media-3"}]
2	Product-2	{"id": "102", "medias": [6, 1]}	[{"6": "Media-6"}, {"1": "Media-1"}]

fiddle

Try use JSON_UNQUOTE and JSON_EXTRACT and JOIN them. Extract the ID's from product_media_ids and join with media table while maintaining their order.

SELECT 
    P.*,
    JSON_ARRAYAGG(M.media_url ORDER BY FIND_IN_SET(M.media_id, JSON_UNQUOTE(JSON_EXTRACT(P.product_media_ids, '$')))) AS image_array
FROM products AS P
LEFT JOIN media AS M ON JSON_CONTAINS(P.product_media_ids, CAST(M.media_id AS JSON), '$')
GROUP BY P.product_id;

OR you can just direct it without using JSON_EXTRACT as below,

SELECT
    P.*,
    JSON_ARRAYAGG(M.media_url ORDER BY FIND_IN_SET(M.media_id, JSON_UNQUOTE(P.product_media_ids))) AS image_array
FROM products AS P
JOIN media AS M ON JSON_CONTAINS(P.product_media_ids, CAST(M.media_id AS JSON), '$')
GROUP BY P.product_id;