How to filter a table base on json array value in another table

Question

I have 2 tables, one for users and one for posts:

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');

What I want to do is to query the posts of one user based on the following list and on the blocked list.

The far I could go is to transform the following list into a table using a sentinel table that just has numbers from 0 to 1000.

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);

this gives me this

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

But when I try this I just get an empty result

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));

I am using Cloud SQL with MySql 8.0.

In my head (:))) the result that I am expecting would be

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"

Provide sample data (3-5 rows) as CREATE TABLE + INSERT INTO and desired output for this data with explanations. — Akina
– Akina, Commented Mar 18, 2021 at 11:09
.. where owner in .. and owner not in .. Does it is possible that the same uid is included in both following and blocked? — Akina
– Akina, Commented Mar 18, 2021 at 12:32

Akina · Accepted Answer · 2021-03-18 12:42:40Z

1

SELECT posts.*
FROM users u1
JOIN users u2 ON JSON_SEARCH(u1.following, 'one', u2.uid)
--           AND JSON_SEARCH(u1.blocked, 'one', u2.uid) IS NULL
JOIN posts ON u2.username = posts.owner
WHERE u1.uid = @uid

If the same uid may be present in both following and blocked columns, and the posts owned by such user must not be returned, then uncomment.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2399bc226e47b3b93e3e5016908677ee

answered Mar 18, 2021 at 12:42

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to filter a table base on json array value in another table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related