pandas read_json: StringIO, orient and nested records

pandas.read_json turns data stored as JSON into a pandas object, usually a DataFrame. Most practical problems appear before the first calculation: am I passing a path or text, where are the records inside the response, and what should one row represent? I start with those questions. A document can be valid JSON while describing something quite different from the table required for a report.

All data below is synthetic. I checked the examples with Python 3.14.4, pandas 3.0.6 and NumPy 2.5.4. Each code block runs independently. The fixtures live in memory or a temporary directory, so you do not need a working API, an access token or a downloaded file. These examples demonstrate reading and checking data; they do not pretend to implement a complete client for an arbitrary service.

Wrap JSON text in StringIO

If you have a string containing JSON data, wrap it in StringIO. The result behaves like a text file for the reader. A plain string passed as the first argument represents a path or a supported address. Passing literal JSON strings directly was deprecated in pandas 2.1 and removed in pandas 3.0. An old example can therefore attempt to open a file whose name looks like the entire JSON document.

# example: records
from io import StringIO
import pandas as pd

payload = '[{"id":"001","units":2},{"id":"002","units":null}]'
df = pd.read_json(
    StringIO(payload), orient="records",
    dtype={"id": "string", "units": "Int64"}, convert_dates=False,
)
print(df["id"].tolist())
print(df["units"].tolist())
print(df.shape, int(df["units"].isna().sum()))

The output is ['001', '002'], [2, <NA>] and (2, 2) 1. The identifiers remain strings, while the missing measurement uses the nullable integer type Int64. I do not replace null with zero: nothing in this example says that an unknown measurement means a measurement of zero. Notice convert_dates=False as well. None of these fields should be interpreted automatically as a date.

The read_json documentation explains the accepted inputs and parameters. The pandas 3.0 changes describe the removed behavior. StringIO does not transform the JSON structure or select records within it. It only adapts text to an interface expecting a data source. Keep that limited responsibility in mind when diagnosing an unexpected table.

A file path and file contents serve different purposes

When the data already exists on disk, pass the path. You do not need to load the entire file into a string and wrap it again. That works for a small sample, but duplicates work and can require additional memory. I build the path with Path; the JSON representation remains the same as in the text example.

# example: file
from pathlib import Path
from tempfile import TemporaryDirectory
import pandas as pd

with TemporaryDirectory(prefix="json-reading-") as folder:
    path = Path(folder) / "records.json"
    path.write_text('[{"city":"Łódź","units":0}]', encoding="utf-8")
    df = pd.read_json(path, orient="records", encoding="utf-8")
    print(df.loc[0, "city"], int(df.loc[0, "units"]))

The output is Łódź 0. The fixture is created only in a temporary directory, which is removed after the block finishes. For a real import, agree on the file location and encoding. A path access failure is different from an invalid JSON structure. Likewise, a decoding failure does not automatically mean the document contains the wrong collection of columns.

The function also supports certain URLs and file storage systems, but that does not remove the need to handle retrieval. An API can return an error page, an envelope containing metadata or only the first page of results. In an API pipeline, I separate obtaining the response from turning it into a table. That gives each check a clear place: response status, pagination completeness and selection of the correct records field.

Orient describes the table representation

orient tells pandas how the JSON structure represents rows, columns and the index. With records, the outer list contains an object for each row. With index, outer keys are row labels and their values are objects containing fields. With columns, outer keys identify columns instead. The parameter must match what the producer actually wrote.

# example: orientations
from io import StringIO
import pandas as pd

by_index = '{"row_a":{"units":2},"row_b":{"units":3}}'
by_split = '{"index":["row_a","row_b"],"columns":["units"],"data":[[2],[3]]}'
a = pd.read_json(StringIO(by_index), orient="index")
b = pd.read_json(StringIO(by_split), orient="split")
print(a.equals(b), a.index.tolist(), a["units"].tolist())

The result, True ['row_a', 'row_b'] [2, 3], confirms that two different representations reconstructed the same table. The default orientation for a DataFrame is columns, but I specify it in these examples. This reduces dependence on defaults and makes the reader easier to compare with the code that wrote the file.

I do not try orientations until an exception disappears. The result matters: column names, row count and the types of values inside cells. A table containing one column of dictionaries can be technically valid and still unsuitable for the intended grouping operation. When writing data with to_json, agree on the representation at both ends of the exchange.

Open an API envelope before building the table

A common API response is an object with a data key containing a list of records, plus other fields describing the response. That outer object is not itself a list of records. The orient parameter does not act as a path to an arbitrary location within a document. Confusing these two responsibilities produces a different table from the one the surrounding explanation promises.

# example: envelope
import json
import pandas as pd

payload = '{"data":[{"id":"A","buyer":{"city":"Łódź"}},{"id":"B","buyer":{"city":"Gdańsk"}}],"page":1}'
document = json.loads(payload)
records = document["data"]
if not isinstance(records, list):
    raise ValueError("data must be a list")
df = pd.json_normalize(records)
print(df.columns.tolist())
print(df["buyer.city"].tolist())

You get ['id', 'buyer.city'] and ['Łódź', 'Gdańsk']. First, json.loads turns text into ordinary Python structures. I then select the list and flatten its nested fields with json_normalize. The function documentation also explains record_path and metadata. For a list of already flat dictionaries, DataFrame(records) is often sufficient.

Flattening a dictionary within one record differs from expanding a list of items into multiple rows. If one request contains several items, an expanded row may represent an item rather than a request. Counting rows will then answer a different question. Before choosing record_path, I define the unit represented by a row and identify the parent information that needs to accompany every expanded item.

Check types and missing values after reading

JSON distinguishes strings, numbers, booleans and null, but it does not store the full pandas type system. It has no separate type for a textual identifier or a general declaration of nullable integers. I specify dtype to make the intended interpretation explicit. I then check the resulting table instead of assuming that configuration alone implements every data quality rule.

There is also a difference between an omitted field and a field explicitly containing null. After a list of records becomes a table, both may appear as a missing value in the same column. If that distinction matters, inspect key presence in the raw records before constructing the DataFrame. Once the information has been lost, isna() cannot reconstruct how the source represented the absence.

Mixed data can produce a broader type than you expected. Inspect dtypes, missing counts and representative values, especially identifiers that resemble numbers. Leading zeroes lost through numeric conversion will not return after a later astype("string"). Establish the identifier type before reading, or retain it while parsing the raw structure first.

I do not replace every missing value with an empty string simply to make the table look tidier. A numeric column can change type or become unsuitable for calculations. Choose a rule for the specific meaning of the absence, then inspect its effect. A syntactically valid table does not prove that a zero measurement and an unknown measurement remain distinguishable.

Convert dates explicitly when the contract requires it

read_json can convert selected date columns, including through convert_dates. For a source with a strict contract, I often separate reading from date conversion. This preserves visibility into the original strings and gives me a separate place to specify the format, timezone and invalid-value behavior. It also makes a producer’s unexpected date format change easier to diagnose.

# example: dates
from io import StringIO
import pandas as pd

payload = '[{"observed_at":"2026-10-24T10:00:00+02:00"},{"observed_at":"2026-10-24T08:00:00Z"}]'
df = pd.read_json(StringIO(payload), orient="records", convert_dates=False)
df["observed_at"] = pd.to_datetime(df["observed_at"], format="ISO8601", utc=True, errors="raise")
print(df["observed_at"].dt.strftime("%Y-%m-%d %H:%M %z").tolist())
print(df.loc[0, "observed_at"] == df.loc[1, "observed_at"])

Both strings produce 2026-10-24 08:00 +0000, and the comparison returns True. They describe the same instant using different offsets. Explicit utc=True gives them a common representation. If input dates lack timezone information, you must first know which timezone they describe. Do not label a local time as UTC merely to suppress a conversion problem.

The to_datetime documentation explains formats and error handling. errors="coerce" is useful when you deliberately want invalid dates marked as missing and counted separately. Here I use raise because the fixture has a strict format. Numeric timestamps also require an agreed unit: seconds and milliseconds describe very different instants when interpreted incorrectly.

Read JSON Lines with lines and chunksize

A regular JSON array is one document, even when formatted over many lines for readability. JSON Lines has a separate document on each line. A file having many lines is therefore not enough reason to enable lines=True. Inspect the format first. The setting should follow the representation, rather than your impression of how large the file looks.

# example: chunks
from io import StringIO
import pandas as pd

payload = '{"id":"A","units":0}\n{"id":"B","units":null}\n{"id":"C","units":3}\n'
sizes = []
rows = missing = total = 0
with pd.read_json(StringIO(payload), lines=True, chunksize=2, dtype={"units": "Int64"}) as reader:
    for chunk in reader:
        sizes.append(len(chunk))
        rows += len(chunk)
        missing += int(chunk["units"].isna().sum())
        total += int(chunk["units"].sum())
print(sizes, rows, missing, total)

The result is [2, 1] 3 1 3: batches contain two and one records, there are three records overall, one missing measurement and a known-value sum of three. The zero has not been discarded. In this interface, chunksize requires lines=True and returns a reader yielding batches. The tiny sample is already in memory; for a real file, you can pass a path instead.

A sum of known values is not an estimate for the missing measurement. Report the missing count separately. For an empty collection or a column containing only missing values, a sum of zero can be misleading on its own. Retain row and missing counts too. To combine batch averages, combine sums and counts of known observations; do not directly average averages from batches of unequal sizes.

Separate parsing errors from data errors

Record how many rows were received, how many were accepted and why any were rejected. Reconcile those counts so that an omitted batch cannot look like a legitimate empty result.

An invalid document can raise ValueError in read_json. json.JSONDecodeError belongs to the standard json parser; it is not a promise that every pandas parser raises the identical exception type. A missing file is another problem again. Preserve the stage and context when handling errors, rather than converting every failure into an empty table.

# example: malformed
from io import StringIO
import pandas as pd

try:
    pd.read_json(StringIO('[{"id":}]'), orient="records")
except ValueError:
    print("invalid JSON")

The output is invalid JSON. I check the exception category rather than copying its complete, version-dependent message. Valid syntax is not sufficient either: a document may omit fields, duplicate identifiers or contain negative measurements. Those rules belong to raw structure validation or checks performed after parsing.

Do not use eval to read JSON. It is a different mechanism with different semantics and unnecessary code execution risk. Python’s standard json module parses documents into Python structures. An external source should also have a size limit appropriate to the process; a valid format does not guarantee that an arbitrary document fits in memory.

Check the table contract before calculating

For a repeated import, I write down the expected columns, their meaning and acceptable missing values. I also check record count and whether the source covers the full requested range. One valid page of a paginated API may still be only a fragment. With this local fixture, we can count everything manually, which provides a useful reference for the program’s output.

# example: contract
from io import StringIO
import json
import pandas as pd

payload = '[{"id":"001","units":0},{"id":"002","units":3}]'
records = json.loads(payload)
if not isinstance(records, list) or not records:
    raise ValueError("Expected a nonempty list")
if any(not isinstance(row, dict) or set(row) != {"id", "units"} for row in records):
    raise ValueError("Unexpected record fields")
if any(type(row["id"]) is not str or type(row["units"]) is not int for row in records):
    raise ValueError("Expected string identifiers and integer units")
df = pd.read_json(StringIO(payload), orient="records", dtype={"id":"string","units":"Int64"})
if set(df.columns) != {"id", "units"}:
    raise ValueError("Unexpected columns")
if df["id"].isna().any() or df["id"].eq("").any() or df["id"].duplicated().any():
    raise ValueError("Invalid identifiers")
if df["units"].isna().any() or df["units"].lt(0).any():
    raise ValueError("Invalid units")
print(len(df), int(df["units"].sum()))

The output 2 3 completes a small, verifiable process. This contract rejects missing measurements, although the first example demonstrated how to preserve them. That is an intentional change in the task’s rule. Do not copy the condition without considering whether it would reject legitimate unknown measurements in your source. Reading a value and accepting it for a calculation are separate decisions.

This example also checks raw types before conversion. The dtype argument is not a validator: an incompatible value may remain in a broader dtype, while a boolean may become a number. The type(...) is int condition deliberately rejects booleans, fractions and strings. This contract requires an integer in the original JSON.

Decide how to respond to schema changes

Suppose the producer adds a source field tomorrow. Comparing the exact set of columns will stop the import, even when existing fields remain valid. That can be appropriate for a strictly agreed exchange file. For an evolving API, you might instead require a minimum set of columns, record additional names and explicitly select fields for subsequent analysis. Choose the policy before an unexpected change interrupts the process.

Removing a required field or changing its meaning is different. If units starts describing packages rather than individual items, the same numeric type and column name will not reveal the change. A data contract therefore includes measurement units, date coverage and row granularity. Technical validation supports that agreement; it cannot infer the producer’s intention from JSON syntax alone.

An empty result also requires interpretation. It may mean a day without events, a filter excluding every record or an incomplete response. I do not add an artificial zero row to make the report appear successful. Decide whether an empty response is acceptable and record its count before transformation. If it must continue through the pipeline, construct the expected schema explicitly while retaining the fact that there were no observations.

With batches, checks may need to operate both within each batch and across the complete import. A duplicate identifier can occur in different batches, so applying duplicated() separately will miss it. Remembering all previously seen identifiers costs memory. For large imports, choose a solution suited to the scale, such as a uniqueness constraint in the destination store, instead of promising constant memory for the whole process.

Keep a record of the library version, expected orientation and checks performed. Another person should be able to reconstruct the import decisions without interpreting an accidental terminal transcript. Do not include customer records or access secrets in that note. A small anonymous fixture preserving the problematic structure and the failing stage is often enough to reproduce a problem.

Before upgrading pandas, run the same fixtures and compare values, types and rejection rules. This can detect a change in text argument handling before it affects a scheduled import. Finishing without an exception is a weak test by itself. Retain examples containing zero, a missing value, a textual identifier and a timezone-aware date, because each exposes a different kind of unintended conversion.

Exercise: change shape, types and completeness

Start with the final example. Duplicate an identifier, remove a required field and insert a negative measurement. Run each variant separately and decide which stage should reject it. Then add another field: our strict contract rejects that too. A different process might accept it, but should still record the schema change and consider whether the new field changes interpretation.

Finally, wrap the records under a data key and deliberately use json.loads followed by list selection. Try an empty list as well. Without records, the reader cannot infer every column from values alone, so accepting an empty result may require an explicit schema. Being able to predict these cases makes read_json a controlled entry point for analysis.