Query JSON array using MySQL

Question

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

Tim Biegeleisen · Accepted Answer · 2019-12-30 16:42:33Z

1

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;

answered Dec 30, 2019 at 16:42

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2019-12-30 16:42:24Z

0

Is this what you are looking for?

rule ->> "$.element[0].total.value"

This gives you the value attribute for the total entity that is the first element in the element array.

This can also be expressed:

json_extract(rule, "$.element[0].total.value")

Demo on DB Fiddle:

select rule ->> "$.element[0].total.value" res
from (
    select cast('{"element":[{"maxDiscount":0,"minAmount":100,"total":{"type":"ABSOLUTE_OFF","value":8}}]}' as json) rule
) t

| res |
| :-- |
| 8   |

answered Dec 30, 2019 at 16:42

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

Query JSON array using MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related