title: MySQL LEFT JOIN ON JSON field
body: <p>I need to select all group names which user is associated with.</p>
  
  <p>Tried every possible solution, but none of them work.</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>This solution works: <a href="https://stackoverflow.com/questions/39818296/using-mysql-json-field-to-join-on-a-table">Using MySQL JSON field to join on a table</a></p>
  
  <pre><code>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), '$')
  </code></pre>
  
  <p>But only when I change all <code>id</code> columns to type <code>INT</code> and change <code>groups</code> JSON value to <code>[9001,9003]</code>, otherwise I get:</p>
  
  <pre><code>ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.
  </code></pre>
  
  <p>Unfortunately, <code>id</code> columns must stay as type <code>VARCHAR</code>.</p>
  
  <p>Expected result:</p>
  
  <pre><code>+--------+----------+-----------+
  | id     | name     | groupname |
  +--------+----------+-----------+
  | U-1001 | John Doe | Group 1   |
  | U-1001 | John Doe | Group 3   |
  +--------+----------+-----------+
  </code></pre>
  
  <p>Please help. Thanks.</p>