Alteryx IN DB tool and JSONb columns

[LauraMendes]

Hi Community!

Does anyone have a list of operators that the Alteryx IN DB supports for querying JSONb in a PostGreS database? It mentions here: https://help.alteryx.com/current/DataSources/PostgreSQL.htm that there are 'certain' operators Alteryx doesn't support but doesn't mention which ones are supported. 

Or if anyone can help me find a way of replicating the below query in a way that works in Altyerx DB that would be amazing:

SELECT * FROM "tablename"

WHERE "Column1" ?| '{Value 1, Value 2, etc...}' 

Thank you 

Laura

[HenrietteH · Accepted solution]

Hi @LauraMendes 

The operators not supported are: ?,?|,?&

Unfortunately, that means that your query won't work. 

All other operators should work though. If you come across another one that does not work, please reach out to support@alteryx.com

There is more information in the PostgreSQL documentation. 

[LauraMendes]

Thanks for your reply and the information provided. I wonder if there was an available alternative to the 'IN' operator that does work for JSONB fields? So far I have to list all of the values as per below and convert them to text. 

Thank you 

LAURA

SELECT * FROM "SCHEMA"."TABLE"
WHERE JSONBCOLUMN:: TEXT LIKE '%Value1%' OR
JSONBCOLUMN::TEXT LIKE '%Value2%' OR

[HenrietteH]

Hi @LauraMendes 

I'm not that familiar with JSONb, going through the documentation, I don't see anything else that would work like an IN operator. Since this is a driver limitation, I'm wondering if a more recent version of the driver might work with the operator? 

The driver in our help documentation is the one we've validated on, but many customers use newer versions successfully. You can download the driver from the PostgreSQL site: https://www.postgresql.org/ftp/odbc/versions/

[LauraMendes]

Ok thanks for your help! My workaround works for now but ill try updating the driver too

Laura