How to retrieve JSON Object data MySQL select query

Question

I Have one table i.e discount_details. I have 2 columns are the ID , DiscountTotal. I want to sum the each record line total json object with in the D array (64,10,10 are the 1st record values) . Like here 64,10,10 are the values,need to sum total value as 84 in the select query of MYSQL. I tried below examples but not working. Can please help on this issue.

SELECT  JSON_EXTRACT(`DiscountTotal`, '$.D') AS total FROM tablename

[Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055), #5 and #3. — Akina, Jun 22 '23 at 20:11

score 0 · Answer 1 · edited Jun 25 '23 at 15:32

0

Here is the query:

SELECT s.SNO, sum(t.Value)
FROM tablename s, 
     JSON_TABLE(
         JSON_EXTRACT(`DiscountTotal`,'$[0].D'),
         "$[*]" COLUMNS(
           Value INT PATH "$"
         )
       ) t group by s.SNO

edited Jun 25 '23 at 15:32

Aymendps

1,346
4
20

answered Jun 23 '23 at 05:36

rumit mittal

19
3

How to retrieve JSON Object data MySQL select query

1 Answers1