WHERE FIND_IN_SET(1, JSON_UNQUOTE(JSON_EXTRACT(mycolumn, '$.parent[0].child'))) > 0
this query is working
WHERE FIND_IN_SET(1, JSON_UNQUOTE(JSON_EXTRACT(mycolumn, '$.parent[*].child'))) > 0
this query is not working
but when i want to put * instead of 0 my query is not working. i want to search key in all positions. first query only shows "search key" equal to only first (0) json
this is my json in mysql column
{"parent":[ { "child":"1,3", "type":"" },{ "child":"36,41", "type":"1" },{ "child":"52", "type":"1" } ]}
You can use a solution like this:
SELECT *
FROM table_name
WHERE FIND_IN_SET(1, REGEXP_REPLACE(JSON_EXTRACT(column_name, '$.parent[*].child'), '[\\[\\]\\" ]', '')) > 0
JSON_EXTRACT returns a single value or a JSON array with all found values. The JSON array is not valid to use on FIND_IN_SET. You first have to remove the [, ] and " to get the comma separated list of all found values.
You can also use JSON_SEARCH to solve this:
SELECT *
FROM table_name
WHERE JSON_SEARCH(column_name, 'one', '%,1,%', NULL, '$.parent[*].child') IS NOT NULL OR
JSON_SEARCH(column_name, 'one', '1,%', NULL, '$.parent[*].child') IS NOT NULL OR
JSON_SEARCH(column_name, 'one', '%,1', NULL, '$.parent[*].child') IS NOT NULL