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: http://sqlalche.me/e/f405)
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.
Thanks!