How to get related items by id inside of json integer array column in MySQL

Question

My goal is how to get a relation between a column that have references in a json array to other one. In a simplified way, I have two tables:

table_a
| id  | references |
|-----|------------|
| 1   |  "[1,3]"   |
| 2   |  "[2,3]"   |

Whose references is a json array of integers and table b

table_b
| id  | name     |
|-----|----------|
| 1   | "item 1" |
| 2   | "item 2" |
| 3   | "item 3" |

So, I would like to get all items of table B related to an item of table A with id, for example, 1 that have their ids in the column references integer array (as json).

Something like this:

|-----|----------|
| 1   | "item 1" |
| 3   | "item 3" |

I have been trying to achieve this with json_contains, json_extract, json_search, etc. from docs and I think that problem is in the way to match values inside of json integers array.

For example:

SELECT JSON_SEARCH((select references from table_a where id=1), 'one', '3');

must return something but always return NULL and I dont understand way. Also I tried with 3 without quotes.

¿Any idea?

My current version of MySQL is 5.7.25

Thanks in advance.

Minimal code to reproduce:

select version();

CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `references` json NULL
);

CREATE TABLE `table_b` (
  `id` int(11) NOT NULL,
  `name` text NULL
);


INSERT INTO `table_a` (`id`, `references`) VALUES
(1, '\"[1,3]\"'),
(2, '\"[2,3]\"');

INSERT INTO `table_b` (`id`, `name`) VALUES
(1, 'item_1'),
(2, 'item_2'),
(3, 'item_3');


SELECT * from table_a;
SELECT * from table_b;

select `references` from table_a where id=1;

SELECT JSON_SEARCH((select `references` from table_a where id=1), 'one', '3');

Sandbox to test: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ac557666852fa94e77fdf87158c9abe0

Show source data as CREATE TABLE + INSERT INTO scripts (or create online fiddle). Show desired output as formatted textual table. — Akina, Nov 10 '20 at 08:09
Why are you using a json array instead of a regular, normalised schema? — Strawberry, Nov 10 '20 at 08:17
@Strawberry I know it. But it is the requirement of the data model right now. If can't achieve that I will refactor it. — Juan Antonio, Nov 10 '20 at 08:48
At the end. I changed the model because this way present a lot of problems. I never remember KISS principle. Thanks all for you help. — Juan Antonio, Dec 11 '20 at 12:19

score 1 · Accepted Answer · answered Nov 10 '20 at 09:26

Incorrect (but checked successfully by JSON_VALID function) JSON.

The solution is monstrous:

SELECT table_b.*
FROM table_a, table_b
WHERE table_a.id = 1
  AND JSON_SEARCH(REPLACE(REPLACE(REPLACE(REPLACE(table_a.references, '"', ''), '[', '["'), ']', '"]'), ',', '","'), 'one', table_b.id) IS NOT NULL

fiddle with some additional queries which explains the problem.

score 1 · Answer 2 · answered Nov 10 '20 at 09:48

You can use next query as solution:

select
    table_a.*,
    table_b.*
from table_a 
join table_b on JSON_CONTAINS(
    CAST(TRIM('"' FROM `references`) as JSON), 
    CAST(table_b.id as JSON)
)
where table_a.id=2;

Because your references field is not valid JSON type you need to convert it into JSON and after this, JSON_CONTAINS functions can be used.

Try it here

score 1 · Answer 3 · answered Nov 10 '20 at 09:52

You can get rid of quotes wrapping up the array first, and then concatenate the resulting integers with item_ substrings along with using auxiliary subqueries one of which generates rows in order to consecutively get each members of the array such as

SELECT b.*
  FROM
  (
    SELECT  @i := @i + 1 AS rn,
            CONCAT('item_',JSON_EXTRACT(JSON_UNQUOTE(`references`), 
                                        CONCAT('$[',@i-1,']'))) AS name
      FROM information_schema.tables 
     CROSS JOIN `table_a` AS a
     CROSS JOIN (SELECT @i := 0) r
     WHERE @i < JSON_LENGTH(JSON_UNQUOTE(`references`)) ) AS a
  JOIN `table_b` AS b  
    ON b.`name` = a.name

Demo

How to get related items by id inside of json integer array column in MySQL

3 Answers3