Problem with the sorting in MYSQL with use INNER JOIN-CodePudding

I have problem with query, I can't corretly write sorting with method INNER JOIN. I Have three tables:

PRODUCT:

 ---- --------------------------------- ------------ 
| id | name                            | created_at |
 ---- --------------------------------- ------------ 
|  1 | Broth                           | 1673625572 |
|  2 | Skyr                            | 1673982452 |
|  3 | Hamburger                       | 1674060883 |
 ---- --------------------------------- ------------

INGREDIENT

 ---- --------- ------------ 
| id | name    | created_at |
 ---- --------- ------------ 
|  1 | kcal    | 1673982085 |
|  2 | Protein | 1673982085 |
 ---- --------- ------------

and table with relations:

 ---- ------------ --------------- -------- ---------- ------------- ------------ 
| id | product_id | ingredient_id | value  | priority | modified_at | created_at |
 ---- ------------ --------------- -------- ---------- ------------- ------------ 
|  1 |          2 |             1 | 389.00 |        1 |  1673983108 | 1673983108 |
|  2 |          2 |             2 |  71.00 |        1 |  1673983183 | 1673983183 |
|  3 |          1 |             2 |  59.00 |        1 |  1674059830 | 1674059830 |
|  4 |          1 |             1 | 394.00 |        1 |  1674059875 | 1674059875 |
|  5 |          3 |             1 | 366.00 |        1 |  1674060944 | 1674060944 |
|  6 |          3 |             2 |  76.00 |        1 |  1674060944 | 1674060944 |
 ---- ------------ --------------- -------- ---------- ------------- ------------

This is my query:

SELECT
    `product`.`name` AS `name`,
    JSON_ARRAYAGG(JSON_OBJECT('name', `ingredient`.`permalink`, 'value', `_related_product_ingredient`.`value` )) AS `ingredients`
FROM
    `_related_product_ingredient`
        INNER JOIN
    `product` ON `product`.`id` = `_related_product_ingredient`.`product_id`
        INNER JOIN
    `ingredient` ON `ingredient`.`id` = `_related_product_ingredient`.`ingredient_id`

GROUP BY `_related_product_ingredient`.`product_id`;

This is a result

 --------------------------------- -------------------------------------------------------------------------- 
| name                            | ingredients                                                              |
 --------------------------------- -------------------------------------------------------------------------- 
| Broth                           | [{"name": "protein", "value": 59.00}, {"name": "kcal", "value": 394.00}] |
| Skyr                            | [{"name": "protein", "value": 71.00}, {"name": "kcal", "value": 389.00}] |
| Hamburger                       | [{"name": "protein", "value": 76.00}, {"name": "kcal", "value": 366.00}] |
 --------------------------------- --------------------------------------------------------------------------

I would like my result to be sorted by protein value from highest to lowest.

Any advice?

CodePudding user response：

You can sort the result by protein value by adding the following line to your query:

ORDER BY
JSON_EXTRACT(ingredients, '$[1].value') DESC;

So the complete query would be:

SELECT
product.name AS name,
JSON_ARRAYAGG(JSON_OBJECT('name', ingredient.permalink, 'value', _related_product_ingredient.value )) AS ingredients
FROM
_related_product_ingredient
INNER JOIN
product ON product.id = _related_product_ingredient.product_id
INNER JOIN
ingredient ON ingredient.id = _related_product_ingredient.ingredient_id
GROUP BY _related_product_ingredient.product_id
ORDER BY
JSON_EXTRACT(ingredients, '$[1].value') DESC;

This will sort the results by the value of the second element in the ingredients array, which should correspond to the protein value.