How select mysql with condition on nested array json?

user_6597870 0
Viewed 207

i'm create a table have one json column and data of inserted has below structure:

{
   "options" : {
      "info" :  [
                 {"data" : "data1", "verified" : 0},
                 {"data" : "data2", "verified" : 1},
                 ... and more
                ],
     "otherkeys" : "some data..."
   }
}

i want to run a query to get data of verified = 1 "info"

this is for mysql 5.7 comunity running on windows 10

    select id, (meta->"$.options.info[*].data") AS `data`
    from tbl
    WHERE meta->"$.options.info[*].verified" = 1

is expect the output of "data2" but the actual output is nothing.

below query worked perfectly

    select id, (meta->"$.options.info[*].data") AS `data`
    from tbl
    WHERE meta->"$.options.info[1].verified" = 1

but i need to search all item in array not only index 1

how can fix it ?

(sorry for bad english)

mysql arrays json
1 Answers

Try:

SELECT `id`, (`meta` -> '$.options.info[*].data') `data`
FROM `tbl`
WHERE JSON_CONTAINS(`meta` -> '$.options.info[*].verified', '1');

See dbfiddle.

user_13164400
Related