MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

INSERT INTO tab
  (`id`, `name`)
VALUES
  ('1', '{"vals":[{"id":123,"name":""}],"additional":"text"}'),
  ('2', '{"vals":[{"id":123}],"additional":"text"}'),
  ('3', '{"vals":[{"id":123,"name":"this"}],"additional":"text"}'),
  ('4', '{"vals":[{"id":222,"name":"this is the one"},{"id":123}],"additional":"text text"}');
SELECT id, name
FROM tab WHERE JSON_CONTAINS(name->>'$.vals[*].id','123','$') 
AND (name->>'$.vals[*].name' IS NOT NULL AND  NOT `name`->'$.vals[*].name' = JSON_ARRAY("")  )
;


id
name


3
{"vals": [{"id": 123, "name": "this"}], "additional": "text"}

4
{"vals": [{"id": 222, "name": "this is the one"}, {"id": 123}], "additional": "text text"}

id	name
3	{"vals": [{"id": 123, "name": "this"}], "additional": "text"}
4	{"vals": [{"id": 222, "name": "this is the one"}, {"id": 123}], "additional": "text text"}