pandas to_json: records, dates, missing values and indexes

to_json writes a pandas object as JSON. Most often, you will call df.to_json(...) on a DataFrame. I treat an export as an agreement with its recipient: what does one record represent, where is its identifier stored, how are dates expressed and what does a missing value mean? Creating a file alone does not prove that another program will interpret the data as intended.

The examples below run independently and use synthetic data. I executed them with Python 3.14.4, pandas 3.0.6 and NumPy 2.5.4. Files are created only inside temporary directories. I also compare data after reading it again, because valid JSON can lose an index, change a type or represent a date at the wrong precision. Each effect requires its own check.

Without a path, the result is JSON text

This method belongs to a DataFrame or Series; it is not called as pd.to_json. When you omit path_or_buf, it returns a string. You can pass that string to another function, save it or parse it for inspection. Here I use a list of records and restrict the table to two clearly named columns.

# example: records
import json
import pandas as pd

df = pd.DataFrame({"id": ["001", "002"], "units": [0, 3]})
text = df.to_json(orient="records")
print(text)
print(json.loads(text) == [{"id": "001", "units": 0}, {"id": "002", "units": 3}])

The text is [{"id":"001","units":0},{"id":"002","units":3}], and the comparison returns True. Identifiers are strings, while measurements remain numbers. json.loads checks the parsed structure, so the test does not depend on whitespace or indentation. This is often more useful than requiring the entire file to have exactly the same textual representation.

JSON text and a Python object are different things. After to_json, indexing the string does not select records as it would in a list of dictionaries. If an HTTP library expects an object to serialize itself, supplying already serialized JSON can lead to double encoding. Check the receiving interface and decide which component is responsible for turning structures into text.

Choose orient for the recipient’s needs

For a DataFrame, the default orientation is columns: outer keys identify columns and inner keys identify index labels. records produces a list of row objects, convenient for many exchange contracts. split separates index labels, column names and values into different fields. No representation is universally best; the recipient must understand the selected one.

The to_json documentation explains orientations and index restrictions. Formats using labels as keys need attention when labels repeat. A JSON object is not a useful representation for several distinct records under the same key. If the index does not identify records, consider an explicit identifier column.

The values representation can be compact, but without names the recipient must learn column order elsewhere. Swapping two columns can then change the meaning without causing a parser error. For collaboration, I prefer a representation whose contract can be checked on a small sample. Saving a few characters rarely compensates for leaving field interpretation implicit.

Move a meaningful index into a column

orient="records" does not write index labels. If the index is merely a row counter, that may be intended. If it contains a case or product key, omitting it loses useful information. Name the index and move it into a column before export. The identifier then becomes an ordinary field in every record.

# example: index
import json
import pandas as pd

df = pd.DataFrame({"units": [0, 3]}, index=pd.Index(["A", "B"], name="record_id"))
without_key = json.loads(df.to_json(orient="records"))
with_key = json.loads(df.reset_index().to_json(orient="records"))
print(without_key)
print(with_key)

The first output is [{'units': 0}, {'units': 3}]. The second also contains record_id with values A and B. Check whether the table already has a column with the index’s name. Resolve a naming conflict deliberately; an automatic rename without agreement can break the recipient’s contract.

I do not treat row order as a replacement for an identifier. The recipient may sort, filter or combine records, so “the second row” is not a stable description of an object. A durable key helps compare exports and identify added, removed or changed records. A fresh positional index after reading usually does not provide that guarantee.

Missing values become null

JSON has null, but no pandas pd.NA and no standard JSON value named NaN. to_json serializes missing values as null. There is no null_value parameter for changing that representation. An older example using it failed at the method call, so we use a complete fixture with an explicit nullable dtype instead.

# example: missing
import json
import pandas as pd

df = pd.DataFrame({"units": pd.Series([0, pd.NA], dtype="Int64")})
text = df.to_json(orient="records")
print(text)
print(json.loads(text)[1]["units"] is None)

The output is [{"units":0},{"units":null}] and True. After parsing with Python’s standard json module, null becomes None. Zero remains zero. This distinction matters for sums, averages and completeness checks. An unknown measurement does not become a measured zero simply because the recipient prefers a column without empty cells.

If a contract requires a textual missing marker, prepare it separately and describe the type change. The string "null" is text; null is a JSON null value. They are not equivalent. Also check whether the source distinguishes an unknown measurement, a nonapplicable field and a retrieval failure. A common missing marker may already have collapsed those distinctions before export.

Force_ascii controls character representation

With the default force_ascii=True, non-ASCII characters are written using escape sequences. force_ascii=False keeps them directly readable. Both representations should parse into the same city name. This setting does not translate text, remove Polish letters or select the recipient’s file encoding on its behalf.

# example: unicode
import json
import pandas as pd

df = pd.DataFrame({"city": ["Łódź"]})
escaped = df.to_json(orient="records", force_ascii=True)
readable = df.to_json(orient="records", force_ascii=False)
print(readable)
print("\\u" in escaped, json.loads(escaped) == json.loads(readable))

The readable text is [{"city":"Łódź"}], and both comparisons return True. I check for a Unicode escape and compare the parsed objects instead of copying a potentially mistaken escaped representation. For a file people will inspect in an editor, force_ascii=False may be helpful. The bytes still need to be written and read with the correct encoding, such as UTF-8.

Indentation controlled by indent changes readability, not logical content. Extensive indentation increases file size. It is helpful when reviewing a small sample but may be unnecessary for a large export. Comparing parsed objects distinguishes formatting changes from data changes and avoids reporting a false discrepancy caused only by whitespace.

Write dates explicitly as ISO 8601

JSON has no separate date type. A date is represented as text or a number according to an agreement. In pandas, the format depends on parameters and orientation; the epoch format has been deprecated since pandas 3.0. For new examples, I specify date_format="iso" and choose precision explicitly. The recipient must still know that the field represents an instant.

# example: dates
import json
import pandas as pd

df = pd.DataFrame({"observed_at": pd.to_datetime(["2026-10-24T10:00:00+02:00"], utc=True)})
text = df.to_json(orient="records", date_format="iso", date_unit="ms")
value = json.loads(text)[0]["observed_at"]
print(value)
print(pd.Timestamp(value) == df.loc[0, "observed_at"])

The value is 2026-10-24T08:00:00.000Z, and the comparison returns True. Before exporting, I converted the timestamp to UTC. The output preserves the instant but not its original presentation with the +02:00 offset. If the recipient also needs a local timezone name, store it in a separate agreed field.

Here, date_unit="ms" determines output precision. Source data with microsecond or nanosecond precision may need a finer unit and a compatibility test with the recipient. For timezone-naive inputs, establish their meaning first. Labeling a local time as UTC is not conversion. Include a missing timestamp and a time close to a day boundary in your test fixtures.

Verify a file by reading what was written

With a destination path, the method writes data and returns None. That differs from a call without a path, which returns text. Do not treat the return value as file contents. This fixture creates a temporary file, reads it using the standard parser and checks the data actually stored on disk.

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

with TemporaryDirectory(prefix="json-writing-") as folder:
    path = Path(folder) / "records.json"
    df = pd.DataFrame({"city": ["Łódź"], "units": [0]})
    result = df.to_json(path, orient="records", force_ascii=False)
    saved = json.loads(path.read_text(encoding="utf-8"))
    print(result is None, saved == [{"city": "Łódź", "units": 0}])

The result is True True. For a production process, also check destination location, available space and repeated-run behavior. The default write operation does not automatically maintain version history. If another process reads the file concurrently, consider preparing a complete temporary file and replacing the destination in a controlled way supported by the filesystem.

I do not export an entire working table merely because its columns are available. Select the fields agreed with the recipient. An analytical table may contain helper identifiers, notes or validation columns that the recipient does not need. Explicit selection also prevents a newly added upstream column from silently expanding the export contract.

JSON Lines writes separate records

lines=True works with orient="records". Each record becomes a separate document on one line. This is different from a normal JSON array formatted with indentation. The recipient needs a parser supporting that arrangement or must read lines individually. It is useful for batch processing and appending additional records.

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

with TemporaryDirectory(prefix="json-lines-") as folder:
    path = Path(folder) / "records.jsonl"
    pd.DataFrame({"id": ["A", "B"]}).to_json(path, orient="records", lines=True)
    pd.DataFrame({"id": ["C"]}).to_json(path, orient="records", lines=True, mode="a")
    records = [json.loads(line) for line in path.read_text(encoding="utf-8").splitlines()]
    print([row["id"] for row in records], len(records))

The output ['A', 'B', 'C'] 3 confirms the initial write and the appended third record. In this interface, mode="a" is supported with lines=True and orient="records". Do not append two independent JSON arrays to a file and expect them to form one valid combined array. Concatenating text does not perform that structural transformation.

Appending does not guarantee record uniqueness. Running the same batch again can append duplicates, so the process needs its own identification and acknowledgement rules. JSON Lines separates a stream into records; it does not provide transactions, locks or protection against every write failure. Choose file publication behavior to match how the recipient consumes it.

Numeric precision and table types are separate concerns

double_precision controls decimal places when serializing floating-point values, with a maximum accepted value of fifteen. It cannot recover precision lost earlier or turn an approximate numeric type into an exact decimal model. Establish the recipient’s supported precision and the acceptable comparison tolerance for the particular measurement before exporting.

Likewise, orient="table" includes a schema, but does not promise to preserve every Python object in every library. The build_table_schema documentation explains schema metadata. Its pandas_version field describes the schema version, which need not match the installed pandas version. Do not use it as the sole evidence of the execution environment.

A default_handler can help with unsupported objects, but its output becomes part of the exchange contract. Automatically applying str to everything can conceal a problem and lose structure. If the recipient needs exact types, choose an appropriate format and schema. JSON is an exchange representation, not a universal copy of every property of an in-memory table.

Check the result after reading it back

The final fixture combines writing and reading with explicit types. records does not contain the full pandas schema, so the importer receives a dtype mapping. Text identifiers retain leading zeroes, and integer measurements allow missing values. The comparison also includes types and the reconstructed index for this simple table.

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

source = pd.DataFrame({"id": pd.Series(["001", "002"], dtype="string"), "units": pd.Series([0, pd.NA], dtype="Int64")})
text = source.to_json(orient="records")
restored = pd.read_json(StringIO(text), orient="records", dtype={"id": "string", "units": "Int64"}, convert_dates=False)
pd.testing.assert_frame_equal(source, restored)
print(restored["id"].tolist(), restored["units"].tolist())

The result is ['001', '002'] [0, <NA>]. This check does not claim that every table survives the same cycle without configuration. It demonstrates a specific agreement for two columns. Extend the fixture with dates, a business identifier index and optional fields, then define which properties must survive exactly and which may be deliberately normalized.

Verify the contract at several levels

Separate three questions when preparing checks. Can the file be parsed as the declared format? Does it contain the correct records and fields? Do the values have the intended meaning? A parser mainly answers the first. The others require expected counts, known identifiers and rules for types, missing values and units. This makes the evidence provided by each test clear.

Check the difference between an omitted field and a present field containing null. A table has a shared set of columns, so record export generally includes fields with missing values. If an API requires absent fields to be omitted, add a deliberate structure transformation and decide which missing values mean omission. Removing every empty field can change the meaning of an update operation at the recipient.

For an integration test, use the parser the recipient actually runs. Reading with the same pandas library checks our local agreement but does not prove compatibility with every JSON implementation. Pay particular attention to large numeric identifiers, timestamp precision and the distinction between numbers and strings. If an identifier is not used for arithmetic, a textual representation can preserve its characters without numeric interpretation.

Adding an export column changes the contract too. Some recipients ignore unknown fields, while others reject them. Test an explicit field set instead of assuming extra information always helps. Renaming a column is equally significant when another program refers to it by name. A recurring process shared by several teams needs an agreed way to introduce such changes.

When comparing exports, decide whether order matters. A record list preserves order, but some tasks care only about identifiers and values regardless of sorting. In other tasks, order represents an event sequence. Tests should follow that agreement. Do not automatically sort everything if that would conceal an ordering error, or report a failure solely because records were rearranged in a permitted way.

Keep a small expected document alongside its field descriptions. This helps the importer author recognize whether the index is a field or metadata and whether dates include timezone information. Use synthetic examples covering awkward cases rather than copying real records. Keep library versions and test results separate from the data intended for machine consumption.

Exercise: verify the recipient’s contract

Change the index to textual identifiers, add a missing date and include a city column. Write the expected records before exporting. Check what records preserves and what must be moved into a column. Compare reading with and without explicit types too. Record the differences rather than treating the absence of an exception as complete proof of compatibility.

Finally, try empty and single-record exports. Decide whether the recipient accepts an empty list or an empty JSON Lines file. For a fuller exercise, combine read_json, one replace rule and controlled serialization. Row counts, identifiers and missing-value counts should have an explainable result at each stage.