First, you need to read the JSON data from the file.

import json

# Replace 'your_data.json' with the path to your JSON file

with open('your_data.json', 'r') as file:

    json_data = json.load(file)

If your JSON data is nested, you might need to flatten it using pandas.json_normalize or a similar utility to make it suitable for a tabular database like DuckDB.

import pandas as pd

# Flattening JSON data if it's nested

# This step depends on the structure of your JSON data

df = pd.json_normalize(json_data)

Next, initialize DuckDB and create a connection.

import duckdb

# Initialize a DuckDB connection in-memory

# You can also connect to a DuckDB file by passing a file path instead of ':memory:'

conn = duckdb.connect(database=':memory:', read_only=False)

Create a table in DuckDB that matches the structure of your JSON data.

# Define a SQL statement to create a table

# The columns and types should match the data in your JSON

create_table_sql = """

CREATE TABLE my_table (

    id INTEGER,

    name VARCHAR,

    value FLOAT,

    -- Add more columns as necessary

);

"""

# Execute the SQL statement to create the table

conn.execute(create_table_sql)

Insert the data from the JSON file into the newly created DuckDB table.

# Convert the DataFrame to a list of tuples for insertion

# Skip this step if you didn't need to flatten the JSON data

data_to_insert = list(df.itertuples(index=False, name=None))

# Define a SQL statement for insertion

# The placeholders (?) should match the number of columns

insert_sql = "INSERT INTO my_table (id, name, value) VALUES (?, ?, ?)"

# Execute the insert statement for each row in the data

for row in data_to_insert:

    conn.execute(insert_sql, row)

Optionally, you can verify that the data has been inserted correctly by querying the table.

# Fetch the data from the table to verify

result = conn.execute("SELECT * FROM my_table").fetchall()

print(result)

Once you’re done, remember to close the connection to DuckDB.

conn.close()

Notes

• The above guide assumes that your JSON data is an array of objects where each object corresponds to a row in the database table. Adjustments may be needed if your data has a different structure.
• The data types in the CREATE TABLE statement should match the data types in your JSON file.
• If your JSON file is large, consider using batch inserts or transactions to improve performance.
• Always sanitize and validate your data to prevent SQL injection when inserting data into a database.
• This guide uses an in-memory database for simplicity, but you can also persist the data by specifying a file path when connecting to DuckDB.