MySQL JSON_EXTRACT() Query returns null

Question

I have the following JSON-File in a MYSQL Database:

[
{
    "order" : 0,
    "value" : 0.168257
},
{
    "order" : 0.031250,
    "value" : 0.002387
},
{
    "order" : 0.062500,
    "value" : 0.002367
},
{
    "order" : 0.093750,
    "value" : 0.002365
},
{
    "order" : 0.125000,
    "value" : 0.002369
},
{
    "order" : 0.156250,
    "value" : 0.002384
   },
   {
    "order" : 0.187500,
    "value" : 0.002403
   }
]

I would like to Query and get the result for "order"=0.156250 I use the following Query:

JSON_EXTRACT(jsonColumn,'$.order') ... WHERE JSON_EXTRACT(jsonColumn,'$.order') = 0.156250

It is not working. I can only select the Column by doing the following:

Select JSON_EXTRACT(jsonColumn,'$[5].order')

Can somebody tell me how to select the Column without giving the index to the Select statement?

Thanks!

Which version of MySQL?

Nick
– Nick

2019-11-26 08:11:48 +00:00
Commented Nov 26, 2019 at 8:11 — Nick
– Nick, Commented Nov 26, 2019 at 8:11
Also, are the values numeric or strings?

Nick
– Nick

2019-11-26 08:12:02 +00:00
Commented Nov 26, 2019 at 8:12 — Nick
– Nick, Commented Nov 26, 2019 at 8:12
MySQL version 5.7 or 8.0?

Salman Arshad
– Salman Arshad

2019-11-26 08:13:04 +00:00
Commented Nov 26, 2019 at 8:13 — Salman Arshad
– Salman Arshad, Commented Nov 26, 2019 at 8:13
MySQL Version 8.0

Johannes Schätzl
– Johannes Schätzl

2019-11-26 08:34:19 +00:00
Commented Nov 26, 2019 at 8:34 — Johannes Schätzl
– Johannes Schätzl, Commented Nov 26, 2019 at 8:34

Ed Bangga · Accepted Answer · 2019-11-26 08:34:03Z

2

You need to convert this to json_table first before filtering

select *
     from
test t1
cross join 
       json_table(t1.jsonColumn,
         "$[*]"
         columns(
           `Order` numeric(9,6) path "$.order",        
           `Value` numeric(9,6) path "$.value"
         )
       ) t2
where t2.Order = 0.156250

see dbfiddle

for mySQL versions 5.7

select
  *  
from test t1  
cross join ( 
  select  1 as col1 union
  select  2 union
  select  3 union
  select  4 union
  select  5 union
  select  6   
  ) t2
where json_extract(t1.jsonColumn, concat('$[', t2.col1, '].order')) = 0.156250;

see dbfiddle

edited Nov 26, 2019 at 8:34

answered Nov 26, 2019 at 8:23

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

MySQL JSON_EXTRACT() Query returns null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related