Question 183798 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL on JSON array and subquery issues

Q: At present, I have the ID of table_b. How can I query the data corresponding to table_a?

table_a
id value
1 a1
2 a2
3 a3
table_b
id table_a_ids
1 [1, 2]
2 [2, 3]

It’s not effective

select * from table_a where id in (select json_extract(batch_bill_ids, '$') from table_b where id = 1);
Tags:

2

Answers


  1. 0

    Test this:

    SELECT *
FROM table_a
WHERE EXISTS (
   SELECT NULL
   FROM table_b 
   WHERE JSON_CONTAINS(table_b.table_a_ids, CAST(table_a.id AS JSON))
   );
    Login or Signup to reply.
  2. 0

    You may do a join using the member of operator as the following:

    select a.id, a.value
from table_a a join table_b b
on a.id member of (b.table_a_ids)
where b.id = 1

    demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search