CODEWITHSUNDEEP
mysqljson
Martin
Martin

Reputation: 29

MySQL JSON search for value inside array

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.

Upvotes: 0

Views: 88

Answers (1)

Nick
Nick

Reputation: 147146

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

Upvotes: 2

Related Questions