CODEWITHSUNDEEP
mysqljson
thepunter99
thepunter99

Reputation: 1

MySQL WHERE JSON_EXTRACT

I am trying to search for a string in a simple JSON Array e.g.

'["Accounting","Administration & Office Support","Advertising, Arts & Media"]'

SELECT classification 
  FROM Skills.skills 
 WHERE JSON_EXTRACT(classification, '$[0]') = "Accounting";

This work and returns all rows with "Accounting" in position 0.

My understanding is if I were to use as wildcard '$[*]' then this would search all positions. However, this does not return any rows.

What JSON function should I use in this case?

Upvotes: 0

Views: 468

Answers (1)

Nick
Nick

Reputation: 147146

You can use JSON_SEARCH to search the array for the value:

SELECT *
FROM skills
WHERE JSON_SEARCH(classification, 'one', 'Accounting') IS NOT NULL

You can also use JSON_CONTAINS, but you need to be careful to add double quotes around the string to make it a valid JSON scalar:

SELECT *
FROM skills
WHERE JSON_CONTAINS(classification, '"Accounting"')

Demo on db-fiddle

Upvotes: 1

Related Questions