This topic was automatically generated from Slack. You can find the original thread here.
Chaim Krause : I need help figuring out how to make proper rows in a sql table based on JSON objects. My returned JSON object from the prior step has 20 x items. If I sent that array of 20 objects I get twenty rows with a json string. How do I get 20 rows with columns based on what is in items?
Dylan Sather (Pipedream) : Just to confirm, are you sending the data to a table in the SQL Service or another DB?
Chaim Krause : sql service
Chaim Krause : actually, I am not going to do that for this workflow. But I would still like to know how to do it for future reference.
Dylan Sather (Pipedream) : The SQL Service expects a JavaScript object as the payload that you send to $send.sql(), so if I have an array of JavaScript objects, I iterate over the array and send each object as the payload like so:
for (const payload of array) {
$send.sql({
table: "star_wars_characters",
payload,
})
}
See this workflow, for example.
That workflow also shows off a cool trick which you may have known about the SQL service: if I send objects that contain new properties, the SQL service will add that column to the table (previous records would hold NULL values for the new column). So when I send two records to the SQL service:
this.arr = [
{ name: "Luke"},
{ name: "Leia", title: "General" },
]
I see this table structure / data:
Chaim Krause : 1. That is so cool.
2. I feel so stupid when the answer is so easy.
3. Thanks.
Dylan Sather (Pipedream) : haha no worries, this isn’t super obvious so I’m adding this example to our docs!
Chaim Krause : Well, let me add this “reaction”, I was looking for a way to use your “blocks” and what I needed to do was just use code. I guess I am having difficulty determining when to use code and when to look for an action, I think you call them.
Dylan Sather (Pipedream) : Right, we call the pre-built steps actions.
Right now I typically search for available actions by keywords, and if I find one and can pass the parameters required by the action, that works. But there are a few cases where you may need to modify the code for the action, or write your own code step:
• The exact action you want doesn’t exist. If we support the app integration, I’ll find an action similar to what I need, click into its code to edit it, and modify it (changing the API endpoint, adding new parameters, etc.)
• You need to run the action conditionally, or loop through input and run the action for each item in the loop. This is your case. We don’t yet have a great way to run an action within a conditional / loop construct. We’re tracking those requests here and here. In the meantime, I edit the action to add an if statement outside of it (if I want to run it only in certain conditions), or manually add a for loop like I did here.
• You just want to run custom code. Often I just need to run completely custom code for my workflow, to modify the event data, exit early if I get bad input, etc. In that case I’ll add my own Node.js code step.
Chaim Krause : Thanks, that helps.
