Equivalent of Postgres's STRING_TO_ARRAY in SQLAlchemy-CodePudding

I have this function where I take a string and split it into an array of words. It works great in Postgres but I want to convert it to SQLAlchemy and I haven't been able to find a good alternative to STRING_TO_ARRAY. Are there any good work arounds that people have found?

Here is my basic query for reference:

SELECT type, 
 UNNEST(STRING_TO_ARRAY(description, ' ')) AS word                                                                                    
 FROM item

CodePudding user response：

From SQLAlchemy's documentation on SQL and Generic Functions, you should be able to use the function by name directly even if SQLAlchemy doesn't know about it.

Note that any name not known to func generates the function name as is - there is no restriction on what SQL functions can be called, known or unknown to SQLAlchemy, built-in or user defined.

from sqlalchemy import select, func, Table, Column, MetaData, String

metadata_obj = MetaData()

item = Table("item", metadata_obj, Column("description", String))

stmt = select(
    func.unnest(
        func.string_to_array(item.c.description, " ")
        ).label("word")
    )

print(stmt.compile(compile_kwargs={"literal_binds": True}))

# SELECT unnest(string_to_array(item.description, ' ')) AS word
# FROM item

This remains a PostgreSQL specific query, but I don't see how to make it agnostic with both UNNEST and STRING_TO_ARRAY.