mysql – Search in JSON by id and name


I have this table:

id number
name text

row for example:

id name
1 {"vals":({"id":123,"name":""}),"additional":"text"}
2 {"vals":({"id":123}),"additional":"text text"}
3 {"vals":({"id":222},{"id":123,"name":"this is the one"}),"additional":"text text"}
4 {"vals":({"id":222,"name":"this is the one"},{"id":123}),"additional":"text text"}

I want to return the ids where vals(*).id=123 and their other name element is not empty(not other name in the json).
So only this one will turn:

    3 {"vals":({"id":222},{"id":123,"name":"this is the one"}),"additional":"text text"}

I’m not strong with json type queries so I only got:

select id 
from tab
where name ->>"$.songs(0).id"=123

I should have done "$.songs(*).id", but that returns to me (123) and that is not = 123

Appreciate your help!