UNNEST function in MYSQL like POSTGRESQL

Question

Is there a function like "unnest" from POSTGRESQL on MYSQL?

Query (PSQL):

select unnest('{1,2,3,4}'::int[])

Result (as table):

 int |
_____|
  1  |
_____|
  2  |
_____|
  3  |
_____|
  4  |
_____|

Answer 1

Short answer

Yes, it is possible. From technical viewpoint, you can achieve that with one query. But the thing is - most probably, you are trying to pass some logic from application to data storage. Data storage is intended to store data, not to represent/format it or, even more, apply some logic to it.

Yes, MySQL doesn't have arrays data type, but in most cases it won't be a problem and architecture can be created so it will fit those limitations. And in any case, even if you'll achieve it somehow (like - see below) - you won't be possible to properly work later with that data, since it will be just result set. You may store it, of course - so to, let's say, index later, but then it's again a task for an application - so to create that import.

Also, make sure that it is not a Jaywalker case, so not about storing delimiter-separated values and later trying to extract them.

Long answer

From technical viewpoint, you can do it with Cartesian product of the two row sets. Then use a well known formula:

N = d₁x10¹ + d₂x10² + ...

Thus, you'll be able to create a "all-numbers" table and later iterate through it. That iteration, together with MySQL string functions, may lead you to something like this:

SELECT 
  data 
FROM (
  SELECT 
    @next:=LOCATE(@separator,@search, @current+1) AS next, 
    SUBSTR(SUBSTR(@search, @current, @next-@current), @length+1) AS data, 
    @next:=IF(@next, @next, NULL) AS marker, 
    @current:=@next AS current 
  FROM 
    (SELECT 0 as i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as n1    
    CROSS JOIN 
    (SELECT 0 as i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as n2 
    CROSS JOIN 
    (SELECT 
       -- set your separator here:
       @separator := ',', 
       -- set your string here:
       @data      := '1,25,42,71',
       -- and do not touch here:
       @current   := 1,
       @search    := CONCAT(@separator, @data, @separator), 
       @length    := CHAR_LENGTH(@separator)) AS init
    ) AS joins 
WHERE 
  marker IS NOT NULL

The corresponding fiddle would be here.

You should also notice: this is not a function. And with functions (I mean, user-defined with CREATE FUNCTION statement) it's impossible to get result row set since function in MySQL can not return result set by definition. However, it's not true to say that it's completely impossible to perform requested transformation with MySQL.

But remember: if you are able to do something, that doesn't mean you should do it.

Answer 2

This sample fetchs all "catchwords" from Table data, wich are seperated by "," Maximum values in the commaseparated list is 100

    WITH RECURSIVE num (n) AS (
      SELECT  1
      UNION ALL
      SELECT n+1 FROM num WHERE n<100 -- change this, if more than 100 elements
    )
    SELECT DISTINCT substring_index(substring_index(catchwords, ',', n), ',', -1) as value
    FROM data
    JOIN num
      ON char_length(catchwords) - char_length(replace(catchwords, ',', '')) >= n - 1

In newer Version of MySQL/MariaDB you can use JSON_TABLE if you can JOIN the elements:

SELECT cat.catchword, dat.* 
FROM data dat 
CROSS JOIN json_table(concat('[',dat.catchwords, ']')
                    , '$[*]' COLUMNS(
                               catchword VARCHAR(50) PATH '$'
                             )
) AS words