Adding external API data to multiple linked tables


I hope somebody can help point me in the right direction, this is all quite new to me as a non-coder.

I am trying to wrap my head around fetching data from an external API that returns a complex JSON response and then adding that data to multiple tables.

I have watched the video here: External APIs: Adding multiple records from an external API - YouTube

It seems simple enough to fetch the data and add the items from one key in the response to one table but not how to add data from multiple keys in the same response to multiple tables.

The example below is fetching search results for a bunch of products. 1 search result “search_parameters” has many products under it.

I need to save this data to 3 tables, search_parameters to a query table, all products to a product table and unique merchant names from each product to the merchant table.

How might you go about adding this data to multiple tables “within a single API query” in such a way that all records are linked correctly to each other in the different tables?

“success”: true,
“id”: “1”,
“engine”: “main”
“result”: {
“search_parameters”: {
“q”: “Plumbers”,
“location”: “New York”
“products”: [
“title”: “Plumbing Tool 1”,
“price”: “$52.49”,
“merchant”: “Merchant 1”,
“position”: 1
“title”: “Plumbing Tool 2”,
“price”: “$25.00”,
“merchant”: “Merchant 2”,
“position”: 2

What principles and functions do I need to understand to think about this process in the correct way?

Thanks in advance!


1 Like

Hi @jay12 - welcome to the forum, thanks for your first post.

The principles you are going to follow will essentially be the same from that video. Be mindful of the path when you are parsing through the data.
You might consider doing a separate loops if it makes it easier to understand where the data is. When looping through the names, you could apply a unique filter then Adding the Records for to your merchant table names.