Guides / empty array vs null element array

JSON to CSV: Distinguish Empty Array [] From [null] in Every Row

When the same JSON field is an empty array [] in one record and an array containing a null element [null] in the next, a naive CSV exporter writes the same blank cell for both. An explicit array-emptiness column policy records the state, element count, null count and original JSON spelling so a reader can tell them apart and reconstruct the exact source value.

This guide demonstrates a measured six-record fixture exported locally on 2026-11-02 with Python 3.13.14. The source has two empty-array records, two null-element array records, and two non-null-element array records. The explicit exporter writes six CSV rows with six columns; every field was read back and matched exactly (36/36 checks). A naive exporter that writes a single tags column produces blank cells for both [] and [null], making the two indistinguishable.

The six-record fixture

The source array contains six records. The tags field is an empty array [] in R01 and R04, an array with a single null element [null] in R02, an array with two null elements [null, null] in R05, and arrays with non-null string elements in R03 and R06. This alternation is the core problem: empty arrays and null-element arrays both produce blank-looking cells in a naive export.

[
  {"id": "R01", "tags": []},
  {"id": "R02", "tags": [null]},
  {"id": "R03", "tags": ["alpha"]},
  {"id": "R04", "tags": []},
  {"id": "R05", "tags": [null, null]},
  {"id": "R06", "tags": ["beta", "gamma"]}
]

Download the original JSON sample. Per RFC 8259 section 5, an array is an ordered collection of zero or more values. An empty array [] has zero elements. An array [null] has one element whose value is the JSON literal null. These are structurally different values: one has no slots, the other has one slot that holds null. A CSV cell cannot encode that distinction on its own; the exporter must carry it in companion columns.

What a naive exporter gets wrong

A naive exporter writes a single tags column. For an empty array it writes a blank cell. For [null] it also writes a blank cell. For [null, null] it writes two empty strings separated by a comma, which CSV parsing interprets as two empty-string elements. This creates three measurable problems:

The naive CSV output shows these problems concretely. R01, R02 and R04 are all blank cells. R05 produces , which a CSV reader interprets as two empty strings, not two null elements:

id,tags
R01,
R02,
R03,alpha
R04,
R05,
R06,beta,gamma

R05 is particularly misleading: [null, null] becomes a CSV field that parses as two empty strings. A downstream system that compares this to the original JSON would see two string elements instead of two null elements. R01 and R02 are completely identical in the naive output despite having different JSON types.

The explicit array-emptiness column policy

Instead of one bare column, expand the field into five columns that together carry the full structural information:

SCHEMA = ["id", "tags_state", "tags_element_count", "tags_null_count", "tags_value", "tags_json"]

The tags_state column classifies the array into one of four states: "empty_array" (zero elements), "all_null_elements" (at least one element, all null), "mixed_null_elements" (at least one null and at least one non-null), or "all_non_null" (at least one element, none null). The tags_element_count column records the total number of elements. The tags_null_count column records how many of those elements are null. The tags_value column contains the pipe-delimited JSON representation of each element (e.g. null|null for [null, null]), preserving element boundaries without colliding with CSV commas. The tags_json column always stores the exact JSON serialization of the original array.

The tags_json column lets a downstream reader parse the original array back to recover the exact structure. The tags_value column provides a human-readable view of the elements without CSV quoting ambiguity. The tags_element_count and tags_null_count columns provide a quick integrity check: if element_count is 0 and state is "empty_array", the reader knows there were no elements. If element_count is 1 and null_count is 1, the reader knows there was exactly one element and it was null. Python's csv module documentation describes the DictWriter used to write the output; our code supplies the schema before iterating over the input.

Measured export results

The exporter was run locally on 2026-11-02 with Python 3.13.14. Source SHA-256: 228c68f5…ac31e (213 bytes). Output SHA-256: f06b4bee…c694 (327 bytes). The CSV was read back with Python's csv.DictReader and every field compared against the parsed source.

All six rows passed all six checks (36/36). The CSV contains 6 data rows and 6 columns. The tags_value column uses pipe | as an element delimiter to avoid colliding with CSV commas. For R02, tags_value is null; for R05 it is null|null. The tags_json column stores the original JSON serialization, quoted by the CSV writer when it contains commas or double quotes. A reader can distinguish an empty array from a null-element array by checking tags_state, tags_element_count or parsing tags_json.

The exporter code

import json, csv, os
from pathlib import Path

SCHEMA = ["id", "tags_state", "tags_element_count",
           "tags_null_count", "tags_value", "tags_json"]

def classify_array(arr):
    elem_count = len(arr)
    null_count = sum(1 for v in arr if v is None)

    if elem_count == 0:
        state = "empty_array"
    elif all(v is None for v in arr):
        state = "all_null_elements"
    elif null_count > 0:
        state = "mixed_null_elements"
    else:
        state = "all_non_null"

    values = [json.dumps(v, ensure_ascii=False) for v in arr]
    value_col = "|".join(values)
    return state, elem_count, null_count, value_col

def export_explicit(source_path, csv_path):
    with open(source_path, "r", encoding="utf-8") as f:
        raw_text = f.read()
    records = json.loads(raw_text)

    rows = []
    for rec in records:
        row = {col: "" for col in SCHEMA}
        row["id"] = rec["id"]
        tags = rec["tags"]

        if not isinstance(tags, list):
            raise ValueError(
                f"Row {rec['id']}: expected array, "
                f"got {type(tags).__name__}")

        state, elem_count, null_count, value_col = classify_array(tags)
        row["tags_state"] = state
        row["tags_element_count"] = elem_count
        row["tags_null_count"] = null_count
        row["tags_value"] = value_col
        row["tags_json"] = json.dumps(tags, ensure_ascii=False)
        rows.append(row)

    with open(csv_path, "x", encoding="utf-8", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=SCHEMA)
        writer.writeheader()
        writer.writerows(rows)
    return rows, raw_text, records

The exporter classifies each value before writing. An empty array gets state="empty_array", elem_count=0 and null_count=0. An array like [null] gets state="all_null_elements", elem_count=1 and null_count=1. An array like ["alpha", null] would get state="mixed_null_elements", elem_count=2 and null_count=1. The tags_json column always stores the exact JSON serialization of the original array, so a downstream reader can parse it back to recover the precise structure without ambiguity. The exporter uses open(csv_path, "x") for exclusive file creation, refusing to overwrite an existing output.

Read-back verification

The verification step reads the CSV back with csv.DictReader and compares every column against the parsed source. For each row it checks that tags_state matches the expected state, tags_element_count matches the expected count, tags_null_count matches the expected null count, tags_value matches the pipe-delimited representation, and tags_json matches the JSON serialization. All six rows passed all six checks (36/36).

def read_back_verify(csv_path, records):
    with open(csv_path, "r", encoding="utf-8", newline="") as f:
        reader = csv.DictReader(f)
        csv_rows = list(reader)

    for csv_row, src_rec in zip(csv_rows, records):
        tags = src_rec["tags"]
        exp_state, exp_elem, exp_null, exp_value = classify_array(tags)
        exp_json = json.dumps(tags, ensure_ascii=False)
        assert csv_row["tags_state"] == exp_state
        assert int(csv_row["tags_element_count"]) == exp_elem
        assert int(csv_row["tags_null_count"]) == exp_null
        assert csv_row["tags_value"] == exp_value
        assert csv_row["tags_json"] == exp_json

Why [] and [null] are not the same

RFC 8259 section 5 defines an array as an ordered collection of zero or more values. An empty array [] has zero elements: there are no slots, no positions, no values. An array [null] has one element at position 0, and that element's value is the JSON literal null. In a JSON to CSV converter pipeline, conflating them can cause downstream systems to treat a zero-length container as a container with null entries or vice versa. For example, a record with "tags": [] may mean "no tags have been assigned", while "tags": [null] may mean "one tag slot exists but its value is unknown or not applicable". The state and count columns make that structural difference travel with the data.

Reproduce the local example

These are independent Python examples, not measurements of the browser converter. Download the input, exporter, saved CSV, naive CSV and execution evidence into one folder. Use Python 3.12 or later and a new output filename.

python array-emptiness-to-csv.py array-emptiness-fixture.json new-output.csv new-naive.csv new-evidence.json

The exporter reads at most 1,000,000 bytes and rejects inputs over that demonstration policy. That limit is a code policy, not a measured browser limit. The output uses UTF-8 and CRLF record endings. Exclusive file creation prevents overwriting an existing file. Faithful text conversion does not neutralize spreadsheet formulas, so use a suitable text import workflow for untrusted data.

Question: Can I just use the JSON string of the array as the CSV cell value?

You can write json.dumps(tags) into a single CSV cell, and that preserves the distinction: [] stays [] and [null] stays [null]. However, this approach pushes the burden of parsing JSON to every downstream reader, including spreadsheets and simple CSV tools that cannot parse JSON natively. The five-column policy provides typed, directly comparable fields (state, element_count, null_count) that a spreadsheet user can filter and aggregate without parsing JSON, while the tags_json column still carries the exact source value for reconstruction.