Search in Json Array in MySql Table

Question

I am storing the json array in the mysql field called member_details,

[{"religion":null,"caste":["2","3","4"],"sub_caste":["0","1","2"],"family_value":null,"family_status":null}]

Now, I want to search for the members who are having caste of 3. Some times I need to search for sub_caste 2.

How can i achieve that?

I have tried to use json_contains but it is giving me empty results

select * from member where json_contains('member_details', '{"caste" : "2"}')

but it gives empty result.

Tim Biegeleisen · Accepted Answer · 2018-12-20 04:41:24Z

1

Here is one way:

SELECT *
FROM yourTable
WHERE JSON_SEARCH(member_details->"$[0].caste", 'one', '3') IS NOT NULL;

The syntax member_details->"$[0].caste first accesses the first (and only) JSON element in the outer array, using [0]. Then, it accesses the caste key, using .caste. Finally, JSON_SEARCH checks if the value 3 occurs within the caste array.

If you needed to search for sub_caste 2 then you could use similar logic.

edited Dec 20, 2018 at 4:41

answered Dec 20, 2018 at 4:22

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Comments

Niket Joshi · Accepted Answer · 2018-12-20 04:20:00Z

0

select * from member where JSON_EXTRACT(caste, "2");

Can you please try this query for getting values from DB. Reference Link

I hope this helps you.

answered Dec 20, 2018 at 4:20

Niket Joshi

7365 silver badges23 bronze badges

Collectives™ on Stack Overflow

Search in Json Array in MySql Table

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related