Question 19985 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql search value from nested json

I am trying to search userId for 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
  1. also tried with JSON_Table , but not getting exact nested json value in where condition.
Tags:

2

Answers


  1. 0

    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
    22

    Adding a WHERE-clause, to just find the value 22 should not be a problem.

    P.S. Above is tested using MySQL 8.x, see: DBFIDDLE

    Login or Signup to reply.
  2. 0

    Here’s a solution that I tested with MySQL 5.7:

    SELECT id, JSON_EXTRACT(data, REPLACE(path, '$', '$.data')) AS element
FROM (
  SELECT id, JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(data, '$.data[*].userId'), 'one', '22')) AS path
  FROM mytable
) AS j
JOIN mytable USING (id);

    Demo: https://dbfiddle.uk/Hvej9Ucd

    If this seems too difficult or complex, then you should consider if JSON is the best way to store your data. This would be far simpler if you stored data in normal rows and columns, and avoided JSON.

    This is what it might look like if you stored the data normally:

    SELECT * FROM mytable WHERE userId = 22;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search