Accessing data from a webhook request and pass along as the Retool Database resource

💬 Workflows
, ,
1

I have a Workflow with a webhook startTrigger that receives a JSON string. I've managed to strip this JSON string to its values using both JavaScript and Python code (I've tried both and they yield the same result). For instance, I take this value {"value":"A,B,C"} and strip it down to just 'A','B','C', which I then pass along to a query as follows:

select * from table where identifier in ({{ids_strip.data}}).

This evaluates to:

select * from table where identifier in ('A','B','C').

Here, ids_strip is the code that parses the data as desired. My expectation is to pass this into my query to get my result, but every time I do so, it comes back empty. However, when I enter what it evaluates to directly, it fetches the data as expected.

I'm not sure if this will clue someone in but I'm seeing this error:
"POSTGRES_TEMPLATED_IN_EXPRESSION_ERR"
when I search through Google Dev Tools Network requests and find the response payload when the block is executed.

image

I compared the two responses. The left has the static query that it evaluates to when ids_strip is not used. The right is when ids_strip.data is used.

I'm unsure of what I'm doing wrong here, as my query doesn't work when using ids_strip. Is there a more efficient way to capture data from a webhook and pass it along as a query for the Retool database?

2

I ended up figuring this out. The Retool Database when querying and using {{}} notation should pass an array of strings inside the curly brackets. I updated my ids.strip block to return ['A','B','C'] and appended it to my Retool database query like:

SELECT * FROM table WHERE identifier = ANY ({{ids_strip.data}})

That did the trick for me.

Changing to ANY() was recommended here:

1 Like