MySQL JSON search for value inside array

Question

JSON:

{
  "type1": [
    "gI7TyvfUJfdhY1e8HClmqcLprhRxFJcAoqMig2A3",
    "AKCbwJ4KOLpQw1wLAcx5Qxwm2q9ntDPeM0wgLBO8"
  ],
  "type2": [
    "M3CDgTUx7UXSv0nj1pRSHf8gqLrJv4nMEP0l0A7X"
  ]
}

Table structure:

id, name, types

How can i search json field "types" to search for given string "M3CDgTUx7UXSv0nj1pRSHf8gqLrJv4nMEP0l0A7X"

SELECT JSON_EXTRACT(types, '$.*') FROM person where types is not null and JSON_EXTRACT(types, '$.*') like "%M3CDgTUx7UXSv0nj1pRSHf8gqLrJv4nMEP0l0A7X%"

I guess this query is not the best i could do.

What is your expected result? the value you are searching for, or the entire types value? — Nick
– Nick, Commented Nov 20, 2019 at 8:24

Nick · Accepted Answer · 2019-11-21 08:13:39Z

2

You can use JSON_SEARCH to look for types values that contain a particular string, checking that the result is not NULL (indicating a match):

SELECT *
FROM person 
WHERE JSON_SEARCH(types, 'one', '%M3CDgTUx7UXSv0nj1pRSHf8gqLrJv4nMEP0l0A7X%') IS NOT NULL

If you want to get the specific matching value (for example when searching on a subset of the string), you can use JSON_EXTRACT on the unquoted output of JSON_SEARCH:

SELECT JSON_EXTRACT(types, JSON_UNQUOTE(JSON_SEARCH(types, 'one', '%j1pRSHf8gqLrJ%')))
FROM person 
WHERE JSON_SEARCH(types, 'one', '%j1pRSHf8gqLrJ%') IS NOT NULL

Demo on dbfiddle

edited Nov 21, 2019 at 8:13

answered Nov 20, 2019 at 14:34

Nick

147k23 gold badges67 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

MySQL JSON search for value inside array

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related