mySQL WHERE IN from JSON Array
<p>I have a table with JSON data in it, and a statement that pulls out an array of ID's for each row...</p>

<pre><code>SELECT items.data-&gt;"$.matrix[*].id" as ids
FROM items
</code></pre>

<p>This results in something like..</p>

<pre><code>+------------+
|    ids     |
+------------+
| [1,2,3]    |
+------------+
</code></pre>

<p>Next I want to select from another table where the ID of that other table is in the array, similar to the <code>WHERE id IN ('1,2,3')</code> but using the JSON array...</p>

<p>Something along the lines of...</p>

<pre><code>SELECT * FROM other_items 
WHERE id IN ( 
  SELECT items.data-&gt;"$.matrix[*].id" FROM items
);
</code></pre>

<p>but it needs some JSON magic and I cant work it out...</p>