Search in JSON by id and name
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