0

I'm working with MySQL and I have a table named course_definition which has a column named cod_subject_ids and it's a JSON type format and holds data like this:

[ "237", "5074" ]

Now I need to search in this column for specific number such as 237 which already exists.

I tried several ways but none of these return the result:

SELECT *
FROM course_definition
WHERE JSON_EXTRACT(cod_subject_ids, '$.237') IS NOT NULL;

SELECT *
FROM `course_definition`
WHERE JSON_EXTRACT(cod_subject_ids, '$."237"') IS NOT NULL;

SELECT *
FROM `course_definition`
WHERE FIND_IN_SET('237', REPLACE(cod_subject_ids, ' ', '')) > 0;

All of these syntaxes returns null result however the data 237 already exists there!

So how to find the columns that has this data in their json type format of cod_subject_ids field?

Stu
  • 30,392
  • 6
  • 14
  • 33
Pouya
  • 114
  • 1
  • 8
  • 36
  • It would be a lot simpler if you treated mysql as a relational database and normalised you json field into a separate table to model a many-to-many relationship – Shadow Jul 15 '23 at 07:37
  • @Shadow I know that dude but the project is not mine and it's not possible to change it – Pouya Jul 15 '23 at 07:40

0 Answers0