mysql json_extract function search in all position

Question

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" } ]}

The latter call probably does not return a single comma separated list of values. — Shadow
– Shadow, Commented Jul 15, 2020 at 7:20
What does it mean the query is not working? Does it throws some error? Or the output is not expected? What is search key? Won't JSON_CONTAINS be a better fit for you here? — RusArtM
– RusArtM, Commented Jul 15, 2020 at 7:58
question is edited. search key is what i want to search, for exampe i want to search 1 — Cabrail Kerimov
– Cabrail Kerimov, Commented Jul 15, 2020 at 8:18

Sebastian Brosch · Accepted Answer · 2020-07-15 08:52:29Z

3

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

demo on dbfiddle.uk

edited Jul 15, 2020 at 8:52

answered Jul 15, 2020 at 8:36

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql json_extract function search in all position

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related