Query JSON array using MySQL

I’m trying to get the data within the Rule column. It has a value in JSON format. I’m trying to construct the query to get the part which says "value":8.

Column name: Rule.

JSON within column:

{"element":[{"maxDiscount":0,"minAmount":100,"total":{"type":"ABSOLUTE_OFF","value":8}}]}

I’m stuck with this query:

select id, rule->>'$."total"' from table A
order by id desc;

My desired output is…

ID | Value
1A | 8

Answer

You may try using the JSON path $.element[0].total.value here:

SELECT
    id,
    JSON_EXTRACT(rule, '$.element[0].total.value') AS val
FROM tableA
ORDER BY id DESC;