Insert JSON array into database-CodePudding

I have a JSON array of airport objects :

{
    "icao": "1L7",
    "name": "Escalante Municipal",
    "latitude": 37.745361,
    "longitude": -111.570167
  },
  {
    "icao": "1L8",
    "name": "General Dick Stout Field",
    "latitude": 37.140246,
    "longitude": -113.306049
  },
  ...
}

I'm trying to insert this array of JSON objects into database.

I can't find a way to add these all at once. How to provide my JSON file to SQLite and have that parse and add it?

CodePudding user response：

The way I solved this problem was by transforming my JSON array into a CSV file and used the SQLite .import command.

First, I transformed my JSON file using jq like so:

jq -r ".[] | [ .icao, .name, .latitude, .longitude ] | @csv" airports-clean.json > data.csv

Then, using SQLite3, I did the following:

$ sqlite3 "path/to/my/sqlite.db"
sqlite> .mode ascii
sqlite> .separator "," "\n"
sqlite> .import data.csv Airports

Now my database has been correctly written with my JSON array!