# Loading JSON, Flat and Nested

pd.read_json loads a JSON list of records into a DataFrame, lines=True reads JSON Lines with one record on each line, and pd.json_normalize with record_path and meta turns a nested list, such as each vendor's reviews, into one row for each item.

Apprentice · Python · Updated November 19, 2026

By Gregor Dzierzon

## After this lesson you can

- Load a JSON list of records with pd.read_json, and a JSON Lines file with lines=True
- Turn a nested list into one row for each item with pd.json_normalize, using record_path for the list and meta for the fields to copy onto each row

## Key takeaways

- JSON is the text format apps and websites use to hand out data. A list of records, each with the same keys, becomes a table with pd.read_json.
- JSON Lines puts one record on each line. Read it with lines=True; without it, read_json stops with a ValueError about trailing data.
- When each record holds a list, such as a vendor's reviews, read_json leaves the whole list in one cell, where you can't count or average it.
- pd.json_normalize(data, record_path="reviews", meta=["vendor_id"]) makes one row for each review and copies the vendor_id onto each. It takes Python data, so load the file with json.load first.

When data comes from an app or a website, it usually comes as JSON: a weather service's forecast, a game's leaderboard, a fitness tracker's export, an online store's orders. Some JSON is already shaped like a table, and some keeps lists inside its records, which takes one more step. You'll meet both, often in the same project.

The example is Sunnyside Farmers Market's app, where shoppers review the market's vendors. It hands out reviews three ways, and this lesson loads each one. The market and its reviews are our own made-up data.

## A list of records: read_json

**JSON** ("JavaScript Object Notation", read "jay-son") is a text format for data: curly braces hold a record of `"key": value` pairs, and square brackets hold a list. The app's export of the latest reviews, `latest_reviews.json`, is a list of records with the same four keys. Its first record:

```json
{
  "vendor_id": "V10",
  "stars": 4,
  "comment": "Lovely, the line was long.",
  "date": "2026-10-17"
}
```

A list of records is a table already: each record is a row and each key a column. **`pd.read_json`** reads it (pandas is imported as `pd`, read "p d"):

```python
import pandas as pd

latest = pd.read_json("latest_reviews.json")
latest
```

```text
  vendor_id  stars                      comment       date
0       V10      4   Lovely, the line was long. 2026-10-17
1       V16      4  Very good, a little pricey. 2026-10-17
2       V05      2  Sold out when we got there. 2026-10-24
3       V08      5     We come back every week. 2026-10-24
4       V09      5        Worth the early trip. 2026-10-24
5       V09      2    Not as fresh as we hoped. 2026-10-24
6       V15      3       Fine, nothing special. 2026-10-31
```

VS Code draws the table as a grid; this lesson shows it as pandas prints it as text.

## One record per line: JSON Lines

The app also keeps a running log, `review_log.jsonl`, and adds a line each time a review arrives. That's **JSON Lines**: one whole record on each line, with no list around them. Its first two lines:

```json
{"vendor_id": "V10", "stars": 4, "comment": "Lovely, the line was long.", "date": "2026-10-17"}
{"vendor_id": "V16", "stars": 4, "comment": "Very good, a little pricey.", "date": "2026-10-17"}
```

Read as ordinary JSON, the file stops after the first record: `pd.read_json("review_log.jsonl")` ends with `ValueError: Trailing data at position 96`, meaning "there's more after the first record, and I don't know what to do with it". `lines=True` says to read each line as its own record:

```python
log = pd.read_json("review_log.jsonl", lines=True)
log.shape
```

```text
(7, 4)
```

The same seven reviews, as seven rows. Logs and big exports often use JSON Lines, because a program can add a line without rewriting the file.

## A list inside each record

The full export, `reviews.json`, is shaped differently: one record for each vendor, with that vendor's reviews nested inside it as a list. Here's its first vendor, with two of its five reviews:

```json
{
  "vendor_id": "V01",
  "vendor_name": "Green Acre Greens",
  "reviews": [
    {
      "stars": 2,
      "comment": "Sold out when we got there.",
      "date": "2026-04-04"
    },
    {
      "stars": 5,
      "comment": "Ask what's new this week.",
      "date": "2026-05-09"
    }
  ]
}
```

`read_json` makes one row for each vendor, and each vendor's whole list of reviews lands in one cell:

```python
nested = pd.read_json("reviews.json")
nested.head(3)
```

```text
  vendor_id  ...                                            reviews
0       V01  ...  [{'stars': 2, 'comment': 'Sold out when we got...
1       V02  ...  [{'stars': 5, 'comment': 'Ask what's new this ...
2       V03  ...  [{'stars': 4, 'comment': 'Great, but sold out ...

[3 rows x 3 columns]
```

The table is too wide to show, so pandas hid the middle column behind `...`. There's no column of stars to average, because the stars sit inside those lists.

## Flattening with json_normalize

**`pd.json_normalize`** flattens a nested list into rows. It works on Python data rather than a file, so first read the file with Python's `json` module: `json.load` turns JSON text into lists and dictionaries.

```python
import json

with open("reviews.json") as file:
    data = json.load(file)
```

Then name the list to unpack with `record_path`, and the fields to copy onto each of its rows with `meta`:

```python
reviews = pd.json_normalize(data, record_path="reviews", meta=["vendor_id", "vendor_name"])
reviews.head(3)
```

```text
   stars                      comment        date vendor_id        vendor_name
0      2  Sold out when we got there.  2026-04-04       V01  Green Acre Greens
1      5    Ask what's new this week.  2026-05-09       V01  Green Acre Greens
2      2  Sold out when we got there.  2026-06-27       V01  Green Acre Greens
```

Now each review is a row, and each row says whose review it is. Without `meta`, you'd get the stars, comments and dates with no way to tell which vendor they belong to.

![How json_normalize flattens a nested list: Green Acre Greens' record, V01, holds a list of five reviews; record_path="reviews" turns each review into its own row with its stars and date, and meta copies V01 and Green Acre Greens onto all five rows](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/11/892d4d40e08e412ebe2eb286cda72788.webp)

```python
reviews.shape
```

```text
(104, 5)
```

104 reviews of 15 vendors. Maple Lane Market Garden, new this season, has an empty list, so it has no rows at all: a nested list with nothing in it disappears when it's flattened.

## Check yourself

2 questions to try on the lesson page.

## So what

A list of records is a table already, JSON Lines needs `lines=True`, and a list inside each record needs `json_normalize`, with `meta` so every row still says where it came from.

## Key terms

- [JSON](https://www.ask-codey.com/glossary/json)
- [read_json()](https://www.ask-codey.com/glossary/read-json)
- [JSON Lines](https://www.ask-codey.com/glossary/json-lines)
- [json_normalize()](https://www.ask-codey.com/glossary/json-normalize)
- [json Module](https://www.ask-codey.com/glossary/json-module)

## Related

- [Loading a CSV File](https://www.ask-codey.com/lessons/pandas-read-csv)
- [Loading an Excel Workbook](https://www.ask-codey.com/lessons/pandas-read-excel)
- [A First Look: head, info and describe](https://www.ask-codey.com/lessons/pandas-head-info-describe)

Source: https://www.ask-codey.com/lessons/pandas-read-json
