Importing json data to multiple tables using API

[Harpal_Shergill]

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]

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]

Thanks @Raminder_Singh ! I check the thread link.

So the steps would be like this?:

1. Create empty table pub and geo
2. Using API populate geo table first
3. 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]

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

JSON Integrations

Take advantage of Integromat''s JSON parsing tools. Create and parse JSON, convert JSON to XML, aggregate and transform to JSON. Create complex workflows and apps. With Integromat, you are in control.

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

Integromat

Airtable Integrations

Part spreadsheet, part database, and entirely flexible.

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