mysql search value from nested json-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW)

mysql search value from nested json

P粉366946380 2023-09-06 14:49:48

403

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

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

P粉366946380

reply all(1)

P粉5548420912023-09-07 09:42:13 1 floor

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: