JSON_EXTRACT for mutliple values problem (Matching a string in a form of integer array to integer using MYSQL)

Question

table1:

id  languages
2   ["2"]   
3   ["1"]   
9   ["1","2"]

"languages" field is a varchar(255).

Table2:

language_id name
1           English
2           Japanese

"language_id" is int(11).

I want output like one of the following:

Desired Output 1:

id  languages  name
2   ["2"]      ["Japanese"]
3   ["1"]      ["English"]
9   ["1","2"]  ["English", "Japanese"]

OR

Desired Output 2:

id  languages  name
2   ["2"]      Japanese
3   ["1"]      English
9   ["1","2"]  English, Japanese

I tried the following query, which only gets close:

SELECT t2.name, r.*
FROM table2 t2
LEFT JOIN table1 t1
ON t2.language_id=CAST(json_extract(t1.languages, '$[0]') AS UNSIGNED);

The output of the above query:

id  languages  name
2   ["2"]      Japanese
3   ["1"]      English
9   ["1","2"]  English

The second part of the languages array is omitted in a "name" field.

How can I output like either one of Desired Output 1 or 2?

Use `JSON_TABLE()` to conver the arrays to tables. Then you can join these properly. — Barmar, Aug 08 '22 at 21:55
This is why putting JSON in SQL tables is a bad idea. Normalize your tables and it will be easy. — Barmar, Aug 08 '22 at 21:56

score 2 · Accepted Answer · answered Aug 08 '22 at 22:23

Use JSON_TABLE() to split the array into rows. You can then join with this to get all the names, and use GROUP_CONCAT() to combine them into a comma-delimited string.

SELECT t1.id, t1.languages, GROUP_CONCAT(t2.name) AS names
FROM table1 AS t1
JOIN JSON_TABLE(languages, 
                "$[*]" columns (language VARCHAR(9) PATH "$")) AS t1_json
JOIN table2 AS t2 ON t2.language_id = t1_json.language
GROUP BY t1.id

DEMO

JSON_EXTRACT for mutliple values problem (Matching a string in a form of integer array to integer using MYSQL)

1 Answers1