I have a JSON Array field on table A, I want to read the relevant descriptions from another table B. Example: Table A,

Table B,

While reading data from table A, I want the result should be,

Can anyone help me with MySQL query to achieve this?

CodePudding user response：

This query will return your output.

SELECT group_concat(tb.description) FROM table_b tb
WHERE tb.type in (SELECT data_set.types FROM (select SUBSTRING(types, 1, 11) AS TYPES from table_a) AS data_set)

CodePudding user response：

Try this one:

    CREATE TABLE `a` (
      `id` int DEFAULT NULL,
      `type` json DEFAULT NULL
    ) ENGINE=InnoDB;

    INSERT INTO `a` VALUES (
      1, '["C", "B", "T"]'
    );

    CREATE TABLE `b` (
      `type` varchar(8) DEFAULT NULL,
      `description` varchar(16) DEFAULT NULL
    ) ENGINE=InnoDB;

    INSERT INTO b VALUES 
      ("C", "Car"),
      ("B", "Bus"),
      ("T", "Train")  ;



    SELECT d.id, CONCAT('[', GROUP_CONCAT(b.description), ']') AS result FROM (
    SELECT id, `c`.`type`
    FROM a,
    JSON_TABLE(`type`, '$[*]'
    COLUMNS(
       `type` VARCHAR(32) PATH '$'))
    AS c) AS d INNER JOIN b ON (`d`.`type` = `b`.`type`);

Result:

    |  id  |   result |
    -------| ----------
    
    '1', '[Car,Bus,Train]'