Query a JSON column with an array of object in MySQL 2024

Question

I have a json column with the follow array:

[ { "name": "someone", "email": "[email protected]", "passwdHash": "123" }, { "name": "sometwo", "email": "[email protected]", "passwdHash": "456" } ]

I tried the follow query to get the row that has the email "[email protected]" in the "accounts" column, but it returns both "passwdHash":

SELECT JSON_EXTRACT(accounts, '$[*].passwdHash') AS passwdHash FROM serverConfig WHERE json_contains(accounts->'$[*].email', json_array("[email protected]"));

Anyone know how to do the right query?

To get the right JSON MySQL query