Importing json data to multiple tables using API

Harpal_Shergill · ‎Mar 16, 2021

Dear community,

I am new to airtable and would like your assistance on best practice on how to consume a json file and use linked relations within the airtables.

i have a json file with structure like this:

  [
       {
          "title":"first title",
          "publicationDate":"2021-02-02",
          "geo":[
             {
                "country":"US",
                "lat":45,
                "lon":55
             },
             {
                "country":"UK",
                "lat":65,
                "lon":55
             }
          ]
       },
       {
          "title":"sec title",
          "publicationDate":"2021-02-02",
          "geo":[
             {
                "country":"BR",
                "lat":4,
                "lon":5
             },
             {
                "country":"UK",
                "lat":65,
                "lon":55
             }
          ]
       }
    ]

Ideally I would like to have two tables: Pub and Geo. When inserting the above resultset data should get loaded to pub with 2 entries and geo table with 3 entries.

is this possible using API? any example or directions on this would be appriciated.
or
do I need to create an attachment field (for json) first in airtable and then use the logic to split data into multiple table?

Thank you in advance for your help.

Raminder_Singh · ‎Mar 16, 2021

Hi @Harpal_Shergill, this is definitely doable using the API. You do not need to create an attachment field first. You can look in this thread for some guidance on how to create a linked record. For you reference, the REST api is documented here.

Hope this helps.
-Raminder

Harpal_Shergill · ‎Mar 16, 2021

Thanks @Raminder_Singh ! I check the thread link.

So the steps would be like this?:

Create empty table pub and geo
Using API populate geo table first
Using API populate pub table and used the reference link from geo table

This will be one document at a time insert. can this be possible with batch operation?

ScottWorld · ‎Mar 16, 2021

Welcome to the community, @Harpal_Shergill!

Yes, this is possible as a batch operation. You will need to write your own custom JavaScript code (via Airtable’s scripting app or Airtable’s Automations) to communicate with the API and to parse the JSON data into the appropriate tables.

Alternatively, if you want to avoid writing code altogether, you can do what I do when I am parsing JSON files into Airtable for my clients, which is to use Integromat’s JSON parsing tools:

Integromat is a no-code/low-code automation platform that has 100% native support for Airtable’s API:

(Note that I am a professional Airtable consultant and a Registered Integromat Partner, and the Integromat links contain my personal referral code.)