Is there an equivalent function of unnest(postgresql) in mysql?

Question

was working on a sql question which had a simple answer through unnest in postgresql but what i want to know is that can we basically solve it on same grounds in mysql too? question with the dataset is given below

Sort the words alphabetically in 'final.txt' and make a new file named 'wacky.txt'. Output the file contents in one column and the filename 'wacky.txt' in another column.

drop table google_file_store;
create table google_file_store
(
    filename    varchar(40),
    Contents    varchar(1000)
);

insert into google_file_store values ('draft1.txt', 'The stock exchange predicts a bull market which would make many investors happy.');
insert into google_file_store values ('draft2.txt', 'The stock exchange predicts a bull market which would make many investors happy, but analysts warn of possibility of too much optimism and that in fact we are awaiting a bear market.');
insert into google_file_store values ('final.txt', 'The stock exchange predicts a bull market which would make many investors happy, but analysts warn of possibility of too much optimism and that in fact we are awaiting a bear market. As always predicting the future market is an uncertain game and all investors should follow their instincts and best practices.');
commit;

solution provided is

select 'wacky.txt' as filename, string_agg(x.contents, ' ')
from (
    select unnest(string_to_array(lower(contents),' ')) as contents
    from google_file_store
    where filename='final.txt'
    order by 1) x

Any mysql question to this question?

score 0 · Answer 1 · answered Nov 24 '22 at 18:01

MySQL has no array type, so there's no string_to_array() function or unnest() function.

The closest thing is JSON_TABLE() which converts a JSON array into rows. But converting your string of space-separated words into a JSON array is not easy.

select j.word from google_file_store
cross join json_table(
  cast(concat('["', replace(contents, ' ', '","'), '"]') as json), 
  '$[*]' columns (word varchar(50) path '$')) j

Result given your first string as input:

+-----------+
| word      |
+-----------+
| The       |
| stock     |
| exchange  |
| predicts  |
| a         |
| bull      |
| market    |
| which     |
| would     |
| make      |
| many      |
| investors |
| happy.    |
+-----------+

Read:

Is there an equivalent function of unnest(postgresql) in mysql?

1 Answers1