This documentation page is also available as an interactive notebook. You can launch the notebook in
Kaggle or Colab, or download it for use with an IDE or local Jupyter installation, by clicking one of the
above links.
Load structured data from JSON files into Pixeltable tables for
processing and analysis.
Problem
You have data in JSON format—from APIs, exports, or application logs.
You need to load this data for processing with AI models or combining
with other data sources.
Solution
What’s in this recipe:
Import JSON files directly into tables
Import from URLs (APIs, remote files)
Handle nested JSON structures
You use pxt.create_table() with a source parameter to create a table
from a JSON file or URL. The JSON must be an array of objects, where
each object becomes a row.
Setup
%pip install -qU pixeltable
Create sample JSON file
First, create a sample JSON file to demonstrate the import process:
Use create_table with source to create a table directly from a JSON
file:
# Create a fresh directorypxt.drop_dir('json_demo', force=True)pxt.create_dir('json_demo')
Connected to Pixeltable database at: postgresql+psycopg://postgres:@/pixeltable?host=/Users/pjlb/.pixeltable/pgdata
Created directory ‘json_demo’.
<pixeltable.catalog.dir.Dir at 0x1556b2800>
# Import JSON file into a new tablearticles = pxt.create_table( 'json_demo/articles', source=str(json_path), source_format='json', # Explicitly specify format when using local file paths)
Created table ‘articles’.Inserting rows into `articles`: 0 rows [00:00, ? rows/s]
Inserting rows into `articles`: 5 rows [00:00, 538.52 rows/s]
Inserted 5 rows with 0 errors.
# View imported dataarticles.collect()
Import from URL
You can import JSON directly from a URL—useful for APIs and remote data:
# Import from a public JSON URL# Using JSONPlaceholder API as an exampleposts = pxt.create_table( 'json_demo/posts', source='https://jsonplaceholder.typicode.com/posts', source_format='json', # Required for URL sources)
Created table ‘posts’.Inserting rows into `posts`: 0 rows [00:00, ? rows/s]
Inserting rows into `posts`: 100 rows [00:00, 15623.57 rows/s]
Inserted 100 rows with 0 errors.
# View first few rowsposts.head(5)
Import from Python dictionaries
Use create_table with a list of dictionaries as source—useful when
you have data in memory:
# Import from a list of dictionariesevents = [ { 'event': 'page_view', 'user_id': 101, 'timestamp': '2024-01-15T10:30:00', }, { 'event': 'click', 'user_id': 101, 'timestamp': '2024-01-15T10:31:00', }, { 'event': 'purchase', 'user_id': 102, 'timestamp': '2024-01-15T10:32:00', },]event_table = pxt.create_table('json_demo/events', source=events)
Created table ‘events’.Inserting rows into `events`: 0 rows [00:00, ? rows/s]
Inserting rows into `events`: 3 rows [00:00, 988.06 rows/s]
Inserted 3 rows with 0 errors.
# View imported eventsevent_table.collect()
Add computed columns
Once imported, you can enrich the data with computed columns:
# Add a computed column combining title and authorarticles.add_computed_column( summary=articles.title + ' by ' + articles.author)
Source types supported:Nested JSON handling:Nested objects and arrays are stored as JSON columns. You can access
nested fields using Pixeltable’s JSON path syntax in computed columns.