Json data search from MySQL table

Question

My MySQL table contain json column academics.

Table values look like this :

id      academics
--------------------------------------------------------
100     ["CBSE-Afternoon-12-A","CBSE-Morning-12-B"]
200     ["CBSE-Afternoon-12-C","CBSE-Morning-12-D"]
300     ["CBSE-Afternoon-12-E","CBSE-Afternoon-12-F"]

I have to find the id from the above table based on the search key:

CBSE-Morning-12 & CBSE-Afternoon-12

I have tried the below query

SELECT id 
FROM ACADEMIC_TABLE 
WHERE JSON_SEARCH(academics, 'all', 'CBSE-Morning-12%') IS NOT NULL

it returns id: 100,200 correctly.

But I need to search with two keywords like condition in JSON [CBSE-Morning-12 & CBSE-Afternoon-12 ] and return id 100,200,300

Please help me

Thanks, Akina . it work fine...sorry my question is wrong , I have updated question. — bijus
– bijus, Commented Mar 18, 2021 at 6:10

Akina · Accepted Answer · 2021-03-18 06:24:50Z

1

I need to search with two keywords like condition in JSON [CBSE-Morning-12 & CBSE-Afternoon-12 ] and return id 100,200,300

Looking at the sample data - you need the value contained either first or second pattern. If so then you must use OR:

SELECT id 
FROM ACADEMIC_TEBLE 
WHERE JSON_SEARCH(academics, 'one', 'CBSE-Morning-12%') IS NOT NULL
   OR JSON_SEARCH(academics, 'one', 'CBSE-Afternoon-12%') IS NOT NULL;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bdc058111adf7d4200a1471c9873e94c

answered Mar 18, 2021 at 6:24

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Json data search from MySQL table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related