Mysql Json - Fetch perticular object from json array on the basis of given id

Question

I have a field of json type named 'user' in 'users' table containing following data

[{"uid":"213", "name":"Tim", "email":"[email protected]"}, {"uid":"214", "name":"Cook", "email":"[email protected]"}, {"uid":"215", "name":"Peter", "email":"[email protected]"}, {"uid":"216", "name":"Jhon", "email":"[email protected]"}]

I want to fetch user object of given uid used this Query

SELECT * FROM users WHERE json_extract(`user`, "$[0].uid") = "213"

but it returns entire array in user field result that i want is - {"uid":"213", "name":"Tim", "email":"[email protected]"}

tried this also

SELECT * FROM users WHERE json_extract(`user`, "$[*].uid") = "213"`

but it returns NULL

How to find particular object of given uid?

Answer 1

You need to provide JSON_EXTRACT(user, "$[0].uid")" in SELECT too.

Try

SELECT *, JSON_EXTRACT(`user`, "$[0].uid") FROM users WHERE JSON_EXTRACT(`user`, "$[0].uid") = "213"

Referance