Problem working with arrays in Retools Database

Queries and Resources
1

I have a multiselect component that saves employee ids as an array of values in Retools DB. The ids are saved in a single column as an array. I need to match the array of values from the DB against a single value. So for example, when I view an employee profile I can see all the projects their assigned too. I have tried using the following queries with no luck.

SELECT * FROM table_name WHERE column_name  = ANY (ARRAY[single_value]);

SELECT * FROM table_name WHERE 'single_value' = ANY (column_name );

Am I missing something here? These queries should work so I assume it has something to do with stored procedures. Correct?

To get around this limitation I'm saving each ID from the multiselect component in a separate row using a JS Query. I would like to drop this extra DB table if I can query the data directly. Any advice on how to write the queries so I can retreive the data would be appreciated.

2

Might be more to do with how the data is stored - I wasn't aware you could create array column types in the RetoolDB, so if you can share how the column is defined and the structure of the data then that'd help with resolving the query

3

It's a regular text field type that stores values from the multiselect. It looks like this is in Retools DB

[1,6,4,3,2]

I don't think its an array field type. It just stores an array as text. Maybe, Im confused....

4

ahhh, so that does explain why your array functions aren't working as expected because it's text.

You could try to coerce it into an array of integers but that might not work, something like:
select column_name::int[]

string to array could work, but I think the square brackets are going to make that messy, so you'd need to trim those too:
string_to_array(REPLACE(replace(column_name,']',''),'[',''),',')::int[]

At which point I'd probably decide to either store the data in a different way so it's searchable, or select all the data into the UI query and then filter it using JSON SQL queries or Javascript functions.

5