Getting MySQL JSON Array field to join on a table

Question

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

id	types
1	[C,B,T]

Table B,

type	description
C	Car
B	Bus
T	Train

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

id	types
1	[Car, Bus, Train]

Can anyone help me with MySQL query to achieve this?

The end output would be on a web/app page? What MySQL version? — FanoFN
– FanoFN, Commented Oct 4, 2021 at 7:46
@FaNo_FN Query output should be like I mentioned above. MySQL Version: 8.0.23. — Venk
– Venk, Commented Oct 4, 2021 at 8:17
You mean that the types output will retain it's JSON array format right? What I'm curious is the end output and how it's going to be used. I mean, will it end up in a report? — FanoFN
– FanoFN, Commented Oct 4, 2021 at 9:03
@FaNo_FN Sorry, I couldn't understand your question. The intention is to display the actual description of the types on the UI. For that, if I write a query like "SELECT * FROM A", I will get [C, B, T]. This is not understandable in UI. So I want to map [C, B, T] to table B and read the description and return it. — Venk
– Venk, Commented Oct 4, 2021 at 10:00

Felix · Accepted Answer · 2021-10-04 18:00:53Z

1

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]'

answered Oct 4, 2021 at 18:00

Felix

1,8682 gold badges23 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mainul Hasan · Accepted Answer · 2021-10-05 00:08:51Z

0

This query will return your output.

SELECT tb.id, concat('[', 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)

edited Oct 5, 2021 at 0:08

answered Oct 4, 2021 at 9:57

Mainul Hasan

6995 silver badges13 bronze badges

Collectives™ on Stack Overflow

Getting MySQL JSON Array field to join on a table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related