Possible ways to transform ENUM values into JSON_TABLE?

June 13, 2022

Using the following query:

SELECT REPLACE(CONCAT("[", SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6), "]"), "'", '"') AS types
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='myschema' 
  AND TABLE_NAME='tbl_items'
  AND COLUMN_NAME='itemTypes';

I get a result like this one:

+-------------------------+
| types                   |
+-------------------------+
| ['a','b','c','d']       |
+-------------------------+

How can I have a result like this:

+--------------+
| type         |
+--------------+
| a            |
| b            |
| c            |
| d            |
+--------------+

I know about JSON_TABLE, and I do get a static list of what I want with:

SELECT *
FROM JSON_TABLE('["a","b","c","d"]', '$[*]' COLUMNS( type CHAR(1) PATH '$' )) AS tt;

But this does not work:

WITH t AS (
SELECT REPLACE(CONCAT("[", SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6), "]"), "'", '"') AS types
   FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA='myschema' 
    AND TABLE_NAME='tbl_items'
    AND COLUMN_NAME='itemTypes'
  LIMIT 1
)
SELECT *
FROM JSON_TABLE(t.types, "$[*]" COLUMNS( type CHAR(1) PATH "$" )) AS tt;

#1109 – Unknown table ‘t’ in a table function argument

Neither this:

SELECT *
FROM JSON_TABLE((
  SELECT REPLACE(CONCAT("[", SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6), "]"), "'", '"')
    FROM information_schema.COLUMNS
   WHERE TABLE_SCHEMA='myschema' 
     AND TABLE_NAME='tbl_items'
     AND COLUMN_NAME='itemTypes'
   LIMIT 1
), "$[*]" COLUMNS( type CHAR(1) PATH "$" )) AS tt;

#1210 – Incorrect arguments to JSON_TABLE

What I’m doing is it even possible?

>Solution :

This is one of the drawbacks of ENUM — the list of allowed values is stored in a BLOB, and you get to do a "fun" parsing job to get it out if you want to query the discrete values to make a drop-down list of values in your UI or something like that.

You are on the right track, but to use a CTE you must reference that CTE in your query:

WITH t AS (
SELECT REPLACE(CONCAT("[", SUBSTRING(COLUMN_TYPE, 6, LENGTH(COLUMN_TYPE) - 6), "]"), "'", '"') AS types
   FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA='test2'
    AND TABLE_NAME='tbl_items'
    AND COLUMN_NAME='itemTypes'
  LIMIT 1
)
SELECT *
FROM t CROSS JOIN JSON_TABLE(t.types, "$[*]" COLUMNS( type CHAR(1) PATH "$" )) AS tt;

Alternatively, just fetch the ENUM definition as-is, and parse it in your application code.