SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `countries` ( `name` VARCHAR(128), `iso2` CHAR(2) PRIMARY KEY ); INSERT INTO `countries` VALUES ('Russia', 'ru'), ('Ukraine', 'ua'); CREATE TABLE `links` ( `link_id` INT PRIMARY KEY auto_increment, `country` CHAR(2), INDEX (`country`) ); INSERT INTO `links` (`country`) VALUES ('ua'), ('ru'); WITH countries_links AS ( SELECT JSON_OBJECT('name', `name`, 'iso2', `iso2`, 'links', COUNT(`link_id`)) d FROM `links` JOIN `countries` ON `countries`.`iso2` = `links`.`country` GROUP BY `name`, `iso2` HAVING COUNT(`link_id`) > 0 ) SELECT JSON_ARRAYAGG(d) FROM countries_links