Search in JSON by id and name I have this table:
id number
name text

row for example:
id name
1 {&quot;vals&quot;:[{&quot;id&quot;:123,&quot;name&quot;:&quot;&quot;}],&quot;additional&quot;:&quot;text&quot;}
2 {&quot;vals&quot;:[{&quot;id&quot;:123}],&quot;additional&quot;:&quot;text text&quot;}
3 {&quot;vals&quot;:[{&quot;id&quot;:222},{&quot;id&quot;:123,&quot;name&quot;:&quot;this is the one&quot;}],&quot;additional&quot;:&quot;text text&quot;}
4 {&quot;vals&quot;:[{&quot;id&quot;:222,&quot;name&quot;:&quot;this is the one&quot;},{&quot;id&quot;:123}],&quot;additional&quot;:&quot;text text&quot;}

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 {&quot;vals&quot;:[{&quot;id&quot;:222},{&quot;id&quot;:123,&quot;name&quot;:&quot;this is the one&quot;}],&quot;additional&quot;:&quot;text text&quot;}

I'm not strong with json type queries so I only got:
select id 
from tab
where name -&gt;&gt;&quot;$.vals[0].id&quot;=123

I should have done &quot;$.vals[*].id&quot;, but that returns to me [123] and that is not = 123
Appreciate your help!
JSON is always tricky and you have to search a lot of tutorials to get the hang of it.
But you can use normalized table instead of JSON.


CREATE TABLE tab (
  `id` INTEGER,
  `name` JSON
);




INSERT INTO tab
  (`id`, `name`)
VALUES
  ('1', '{&quot;vals&quot;:[{&quot;id&quot;:123,&quot;name&quot;:&quot;&quot;}],&quot;additional&quot;:&quot;text&quot;}'),
  ('2', '{&quot;vals&quot;:[{&quot;id&quot;:123}],&quot;additional&quot;:&quot;text&quot;}'),
  ('3', '{&quot;vals&quot;:[{&quot;id&quot;:123,&quot;name&quot;:&quot;this&quot;}],&quot;additional&quot;:&quot;text&quot;}'),
  ('4', '{&quot;vals&quot;:[{&quot;id&quot;:222,&quot;name&quot;:&quot;this&quot;}],&quot;additional&quot;:&quot;text&quot;}');




SELECT id, name
FROM tab WHERE JSON_CONTAINS(name-&gt;&gt;'$.vals[*].id','123','$') 
AND (name-&gt;&gt;'$.vals[*].name' IS NOT NULL AND  NOT `name`-&gt;'$.vals[*].name' = JSON_ARRAY(&quot;&quot;)  )
;


id | name                                                         
-: | :------------------------------------------------------------
 3 | {&quot;vals&quot;: [{&quot;id&quot;: 123, &quot;name&quot;: &quot;this&quot;}], &quot;additional&quot;: &quot;text&quot;}


db&lt;&gt;fiddle here