CODEWITHSUNDEEP
mysqlmysql-5.7
moses toh
moses toh

Reputation: 13172

How can I do where clause containing json in mysql?

For example, I have a column named json in table A

Json column contains json data like this :

record 1 : {"dept_code": "012", "unit_code": "22"}
record 2 : {"dept_code": "013", "unit_code": "23"}
etc

I want to take the data records with json column that contain dept_code = 012

SELECT * FROM table_A WHERE json = ...(looking dept_code = 012)...

How can I do that?

Note :

I tried to find answers on the site stackoverflow, but I did not find it. So I make this question

Upvotes: 2

Views: 4869

Answers (3)

SlimsGhost
SlimsGhost

Reputation: 2909

Pretty sure JSON_CONTAINS is what you want. View the docs here

I don't have a sandbox to test this right now, but your example would translate to something like:

select * from table_A
where json_contains(json, '012', '$.dept_code')

Since dept_code is a property of the object stored in the json column.

Upvotes: 3

Sujan
Sujan

Reputation: 150

Maybe this can help:

SELECT * FROM table_A where json like '%"dept_code": "012"%';

Upvotes: 3

Mihai
Mihai

Reputation: 26784

 ...WHERE JSON_CONTAINS(`json `, '{"dept_code " : "012"}')

Upvotes: 0

Related Questions