Possible ways to transform ENUM values into JSON

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?

CodePudding user response：

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.