Change json field in mysql to include data from other table

Question

I have these two tables:

CREATE TABLE `config_support_departments` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `support_tickets_filters` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `filter_departments` json DEFAULT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

config_support_departments:

+----+-------------------------+ | id | title | +----+-------------------------+ | 1 | Projects / File Support | | 2 | Sales Support | | 3 | IT Support | +----+-------------------------+

support_tickets_filters:

+----+--------------------+ | id | filter_departments | +----+--------------------+ | 1 | ["2", "3"] | +----+--------------------+

What I need is when query the support_tickets_filters table to also include the title from the config_support_departments table. Hence, the result should be something like this:

+----+-----------------------------------------------+ | id | filter_departments | +----+-----------------------------------------------+ | 1 | {"2":"Sales Support","3":"IT Support"} | +----+-----------------------------------------------+

This would be very easy if you didn't use JSON, but used a third table to store the many-to-many associations. JSON makes it seem easy to store data, but it makes it much harder to query that data later. — Bill Karwin
– Bill Karwin, Commented Dec 23, 2020 at 17:56

Barbaros Özhan · Accepted Answer · 2021-01-04 18:08:35Z

You can use JSON_OBJECTAGG() function along with JSON_TABLE() if DB version is 8.0+

SELECT s.id, JSON_OBJECTAGG(c.id,c.title) AS filter_departments FROM `support_tickets_filters` AS s JOIN JSON_TABLE( s.`filter_departments`, '$[*]' COLUMNS (id INT PATH '$') ) j JOIN `config_support_departments` AS c ON j.id = c.id GROUP BY s.id

For DB version 5.7, you can use one of the DB metadata tables such as information_schema.tables in order to generate index values as 0,1,... upto the length of the array (filter_departments) for extracting the related value from that iteratively

SELECT s.id, JSON_OBJECTAGG(c.id,c.title) AS filter_departments FROM ( SELECT @i := @i + 1 AS n, s.id, JSON_UNQUOTE(JSON_EXTRACT(`filter_departments`,CONCAT('$[',@i,']'))) AS elm FROM `support_tickets_filters` AS s JOIN (SELECT @i := -1) AS iter LEFT JOIN information_schema.tables AS t ON @i < JSON_LENGTH(`filter_departments`) - 1 ) AS s JOIN `config_support_departments` AS c ON s.elm = c.id GROUP BY s.id

Demo

you're welcome @MilenMihalev , I've added the case for DB version 5.7.

Collectives™ on Stack Overflow

Change json field in mysql to include data from other table

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related