Is it possible to link tables with keys stored in JSON column?

Question

I have two tables:

CREATE TABLE jtable (id integer, jdata JSON);
INSERT INTO jtable(id, jdata) VALUES(1, '["1","2","3"]');
INSERT INTO jtable(id, jdata) VALUES(2, '["1","3"]');
INSERT INTO jtable(id, jdata) VALUES(3, '["2","3"]');

id	jdata
1	["1","2","3"]
2	["1","3"]
3	["2","3"]

CREATE TABLE informacoes(id integer, value varchar(10));
INSERT INTO informacoes(id, value) VALUES(1,"valor1");
INSERT INTO informacoes(id, value) VALUES(2,"valor2");
INSERT INTO informacoes(id, value) VALUES(3,"valor3");

id	value
1	valor1
2	value2
3	Valor3

On the table jtable have a column jdata guy JSON in the Mysql.
That column jtable.jdata will always contain contains an array of integers.

I ask you:

It is possible to relate these integers in the column jtable.jdata to the ids table information(informacoes.id), in order to obtain this structure?

jtable id	value information
1	valor1
1	value2
1	Valor3
2	valor1
2	Valor3
3	value2
3	Valor3

I tried doing the INNER JOIN with the result of JSON_SEARCH but I was unsuccessful:

SELECT 
    jtable.jdata,
    informacoes.value,
    JSON_SEARCH(jtable.jdata,'all',informacoes.id)
FROM jtable
INNER JOIN informacoes

Answer 1

Yes, in the Mysql it is possible to relate data of a json document¹ and yes the desired data structure is tangible.

Mysql environment gives your user a function set facilitating the operation with data typified as JSON. Among these functions is:

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

That extracts data from a document JSON and returns them as a relational table with the specified columns.

• expr: This is an expression containing the data JSON. Can be a string, a column or the result of a function.

• path: A path expression to identify a specific element in a JSON document. To see in detail the syntax used read JSON path syntax.

• COLUMNS: evaluates the row origin, finds specific JSON values at the row origin and returns those JSON values as SQL values in individual columns of a relational data row.

• alias is a "nickname" governed by the usual rules for surnames defined in the Names of scheme objects.

Now knowing a little more about tables and data type JSON the problem became a consultation SELECT solved trivial with the use of JOIN between a Table and a JSON table.

Using the data defined in the question:

SELECT jt.id AS 'jtable ID', inf.value AS 'Valor' 
FROM jtable AS jt, 
     JSON_TABLE(
        jt.jdata,                                     --Origem do documento JSON.
        '$[*]' COLUMNS( c1 INT PATH '$')              --Declara coluna c1 cujos os elemento são os elemento do array.
     ) AS jsonT
INNER JOIN informacoes as inf                         --Faz a ligação entre os elementos das tabelas jtable e informacoes... 
ON inf.id = jsonT.c1                                  --relacionando informacoes.id a cada elemento obtido na tabela JSON.
ORDER BY jt.id

jtable ID	Valor
1	valor1
1	value2
1	Valor3
2	valor1
2	Valor3
3	value2
3	Valor3

^{Test the example on Paiza.}

_{[1] json document is a term used in the Mysql manual to refer to a data whose type is JSON. This data can come from hard coded string, from a column in a query or as a result of a function.}