I'm trying to use the results of an API call, which returns an array of emails, in a PostGres query.
I'm trying the following but this is returning no results.
SELECT *
FROM
users as U
LEFT JOIN user_communication_preferences as UCP on U.id = UCP.user_id
where
email in ({{GET_unsubscribe_newsletter.data}})
If I inspect the query's state and look at the 'query' key, I can see that I get the following produced:
"SELECT * FROM users as U LEFT JOIN user_communication_preferences as UCP on U.id = UCP.user_id where email in ('joe+1@test.com','joe+2@test.com')"
The above looks to be creating the correct query and if I try this 'generated' query I am returned results.
Why is this approach not working if it looks to be generating the correct query state? Does anyone else know how I can use an array within an Where In?
Thanks @ScottR but I'm not sure I understand that thread. What is AlaSQL? Is that what Retool uses when creating Queries? I also noticed that this looks to be using 'Query JSON with SQL' as the resource, when I am connecting to a postgres db. You also comment 'I reverted back to direct query of data' what do you mean by this exactly?
Well the solution wasn't working for me using Query JSON with SQL so I used a direct query to the db using the resource.
I am assume you are using Query JSON with SQL or you are not?
If not, have you tried using @>
SELECT * FROM
users as U
LEFT JOIN user_communication_preferences as UCP on U.id = UCP.user_id
where
email @> ({{GET_unsubscribe_newsletter.data}})
I haven't used the 'Query JSON with SQL' resource, how do I connect that to my db, so dev db in this case? If I try this and run my query I just get 'table does not exist', so I guess it's not connected.
I tried your suggested method, using dev database as my resource but get the following result:
message:"operator does not exist: character varying @> unknown"
When you use Query JSON with SQL, you are querying a data result set directly. The data result set would be from a query that uses a resource that is connected to your db. In your case, the data result set is {{GET_unsubscribe_newsletter.data}} so add a Resource Query and select Query JSON with SQL under the Resource panel
Sorry @ScottR I may be misunderstanding you, but the data result set I am wanting to query (in my Where In, {{GET_unsubscribe_newsletter.data}}) is from an external, SendGrid API, and therefore not from a resource connected to my db. I then want to use this dataset to query my db, this is why the above approach isn't working for me as using it I can't see how I define which db to connect to.
