Using json string in SQL query - how to format?

I have a loop that runs against some SQL query results. The results need to be checked against a global artifact to filter out any entries that have been acknowledged. This will tune them out from the report so an analyst won’t waste time. The result of the loop is an array of objects. The objects are fairly simple at this point: a user ID, a device ID, and a couple other fields.

The next thing I want to do is look up each user ID in a separate database for some metadata that’s not available in the first. Ultimately the results from the original SQL query results should be merged with this metadata from the second database. So an object should eventually have that user ID, the device ID, and then we’ll pull in an enrollment date, DOB, an email address, and maybe a few others (again, this is not available in the first DB).

I think I’d like the next step in the WF to be loop to iterate through each element of the array containing the previous results. Inside this loop will be a jq step to pull out just the user ID of the current item, and then a step for a SQL query along the lines of:

select id, date_of_birth, display_name, email_address, enrollment_date from member where id = {{[“get_member_id”].[json_out]}}

I went with that and got an error in my SQL. The where clause is definitely not formatted right:

[SQL: ‘select id, date_of_birth, display_name, email_address, enrollment_date from member where id = [\n “123456789”\n]’] (Background on this error at:

Is there any way to make that json string a little prettier for an SQL query? Or a better way to reference that value within the original array of objects? Any other suggestions? I tried using the R7 string plugin to trim the string, but that didn’t help much. I also don’t really like the idea of making one DB query per array element inside the loop. I’d rather make one query for all the data and then merge the two arrays.


Artifacts have a content output that is always a string that works nice if you are pulling something out of an array or even some handlebar magic, but I’ve started using parse steps to do this because that doesn’t create an extra card for the analyst to see. you can put your handlebar formatted string then use a basic regex to put that in a variable to use in your SQL. Another option would be the Type Converter of string to Object if you are working from JSON it will create outputs for each of your indexes

1 Like