mysql search value from nested json

Question

I'm trying to search for userId value 22 from nested json in mysql column

My json is

'{
     "data": [
            {"calendarId":"11","userId": "12"},
            {"calendarId":"21","userId": "22"}
           ]
    }'

I tried the following syntax:

1. where JSON_EXTRACT(column_field,'$.userId') = 22

2. where
JSON_EXTRACT(
column_field,
'$.data[*].userId'
) = 22

3. Also tried using JSON_Table, but didn't get the exact nested json value in the where condition.

Answer 1

this:

select json_extract('{
     "data": [
            {"calendarId":"11","userId": "12"},
            {"calendarId":"21","userId": "22"}
           ]
    }','$[0].data[*].userId');

gives: ["12","22"]

and this:

select * 
from json_table(json_extract('{"data": [{"calendarId":"11","userId": "12"},{"calendarId":"21","userId": "22"}]}',
                '$[0].data[*].userId'), 
                '$[*]' columns (value int path "$")) x
;

gives:

value
12
twenty two

Add a WHERE clause to find only the value 22 Should not be a problem.

Note The above was tested using MySQL 8.x, see: DBFIDDLE