MySQL 8.0

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

CREATE TABLE person(
   id   INTEGER  NOT NULL PRIMARY KEY 
  ,data json NOT NULL
);
INSERT INTO person(id,data) VALUES (1,'[{"name":"one"},{"name":"two"}]');
INSERT INTO person(id,data) VALUES (2,'[{"name":"three"},{"name":"four"}]');
select p.id, x.name
from person p
cross join json_table(
	p.data,
	'$[*]' columns (name varchar(50) path '$.name')
) x
where x.name <> 'three'


id
name


1
one

1
two

2
four


select *
from (
	select p.id, json_unquote(json_extract(p.data, concat('$[', n.num, '].name'))) name
	from person p
	inner join (select 0 num union all select 1 union all select 2) n
		on n.num < json_length(p.data)
) t
where name <> 'three'


id
name


1
one

2
four

1
two