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
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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:
Add a WHERE clause to find only the value
22Should not be a problem.Note The above was tested using MySQL 8.x, see:DBFIDDLE