如何根据另一个表中的json数组值过滤一个表

我有两个表，一个用于用户，另一个用于帖子：

create database db;
create table if not exists db.users
(
uid       char(10) primary key,
username  char(10),
following json,
blocked   json
);
insert into db.users (uid, username, following, blocked)
VALUES ('uid_0', 'user_0', '["uid_1", "uid_2"]', '["uid_3"]');
insert into db.users (uid, username, following, blocked)
VALUES ('uid_1', 'user_1', '["uid_0", "uid_2", "user_3"]', '[]');
insert into db.users (uid, username, following, blocked)
VALUES ('uid_2', 'user_2', '["uid_0"]', '[]');
insert into db.users (uid, username, following, blocked)
VALUES ('uid_3', 'user_3', '["uid_1"]', '[]');
create table if not exists db.posts
(
id    char(10) primary key,
owner char(10),
text  char(100)
);
insert into db.posts (id, owner, text)
VALUES ('post_0', 'uid_0', 'text_0');
insert into db.posts (id, owner, text)
VALUES ('post_1', 'uid_1', 'text_1');
insert into db.posts (id, owner, text)
VALUES ('post_2', 'uid_2', 'text_2');
insert into db.posts (id, owner, text)
VALUES ('post_3', 'uid_3', 'text_3');

我想做的是根据以下列表和阻止列表查询一个用户的帖子。

我所能做的就是使用一个只有0到1000个数字的sentinel表将下面的列表转换为一个表。

SET @following = (select following
from firestore_mirror.users
where uid = 'userId');
SELECT JSON_EXTRACT(@following, CONCAT('$[', helper._row, ']')) as uid
FROM (SELECT @following AS helper) AS A
INNER JOIN firestore_mirror.t_list_row AS helper
ON helper._row < JSON_LENGTH(@following);

这给了我这个

"value_0"
"value_1"
"value_2"
"value_3"
"value_4"

但当我尝试这个时，我只得到一个空的结果

SET @following = (select following
from firestore_mirror.users
where uid = 'userId');
select *
from firestore_mirror.posts
where owner in (SELECT JSON_EXTRACT(@following, CONCAT('$[', helper._row, ']')) as uid
FROM (SELECT @following AS helper) AS A
INNER JOIN firestore_mirror.t_list_row AS helper
ON helper._row < JSON_LENGTH(@following));

我正在将Cloud SQL与MySql 8.0一起使用。

在我的脑海里(：((我期待的结果将是

select *
from db.posts
where owner in (select following from db.users where uid = 'uid_0')
and owner not in (select blocked from db.users where uid = 'uid_0');

id, owner, text
"post_1", "uid_1", "text_1"
"post_2", "uid_2", "text_2"