CODEWITHSUNDEEP
mysqljsonleft-join
scorpio1441
scorpio1441

Reputation: 3088

MySQL LEFT JOIN ON JSON field

I need to select all group names which user is associated with.

Tried every possible solution, but none of them work.

CREATE TABLE `users` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `groups` json NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `groups` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `users` (`id`, `name`, `groups`) VALUES('U-1001', 'John Doe', '[\"G-9001\", \"G-9003\"]');

INSERT INTO `groups` (`id`, `name`) VALUES('G-9001', 'Group 1');
INSERT INTO `groups` (`id`, `name`) VALUES('G-9002', 'Group 2');
INSERT INTO `groups` (`id`, `name`) VALUES('G-9003', 'Group 3');

This solution works: Using MySQL JSON field to join on a table

SELECT u.id, u.name, g.name AS groupname
FROM users u
LEFT JOIN groups g ON JSON_CONTAINS(u.groups, CAST(g.id as JSON), '$')

But only when I change all id columns to type INT and change groups JSON value to [9001,9003], otherwise I get:

ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.

Unfortunately, id columns must stay as type VARCHAR.

Expected result:

+--------+----------+-----------+
| id     | name     | groupname |
+--------+----------+-----------+
| U-1001 | John Doe | Group 1   |
| U-1001 | John Doe | Group 3   |
+--------+----------+-----------+

Please help. Thanks.

Upvotes: 2

Views: 9145

Answers (1)

scorpio1441
scorpio1441

Reputation: 3088

I was just missing the quotes around g.id:

SELECT u.id, u.name, g.name AS groupname
FROM users u
LEFT JOIN groups g ON JSON_CONTAINS(u.groups, JSON_QUOTE(g.id), '$')

Upvotes: 6

Related Questions