JSON_TABLE

JSON_TABLE

Flattening Documents with JSON_TABLE

JSON_TABLE(doc, path COLUMNS (...)) is a table function: it turns the nodes matched by path into rows with typed columns, so joins, GROUP BY and window functions work on JSON. Because it may refer to p.attributes, it is implicitly LATERAL (Derived Tables and LATERAL).

One row per tag, then an ordinary GROUP BYSQL

SELECT jt.tag, COUNT(*) AS n, GROUP_CONCAT(p.sku ORDER BY p.sku) AS skus
  FROM products AS p,
       JSON_TABLE(p.attributes, '$.tags[*]' COLUMNS (tag VARCHAR(20) PATH '$')) AS jt
 GROUP BY jt.tag HAVING n > 1;

Output

+-------+---+---------------------+
| tag   | n | skus                |
+-------+---+---------------------+
| mysql | 2 | BK-SQL-01,BK-SQL-02 |
| php   | 2 | BK-LAR-01,BK-PHP-01 |
| web   | 2 | BK-LAR-01,BK-PHP-01 |
+-------+---+---------------------+

The comma join acts as an inner join: an empty path yields no rows, so only the 5 tagged products took part; LEFT JOIN JSON_TABLE(...) AS jt ON TRUE kept all 8. Columns can also be FOR ORDINALITY counters, EXISTS PATH flags or NESTED PATH expansions, with fallbacks: on the mug, format VARCHAR(10) PATH '$.format' DEFAULT '"merch"' ON EMPTY gave merch (the default is JSON text), and color TINYINT PATH '$.color' DEFAULT '-1' ON ERROR gave -1, as "black" will not cast.

MENU