Guides / Union-type fields

JSON to CSV: Export Union-Type String and Array Fields Without Losing Shape

When the same JSON field is a string in one record and an array in the next, a naive CSV exporter collapses both into an untyped cell. Declare a union-type column policy that records the kind, count, joined value and original JSON spelling so a reader can reconstruct the exact source shape.

This guide demonstrates a measured four-record fixture exported locally on 2026-10-31 with Python 3.13.14. The source has two string-valued records and two array-valued records, including an array element that contains a comma. The union-type exporter writes four CSV rows with five columns; every field was read back and matched exactly. A naive exporter that joins arrays with commas produces ambiguous output where a reader cannot tell strings from arrays or recover element boundaries.

The four-record fixture

The source array contains four records. The tags field is a string in R01 and R03, and an array in R02 and R04. R03's string value contains a comma, and R04's second array element also contains a comma. These commas are essential for testing whether the export preserves element boundaries.

[
  {"id": "R01", "tags": "single"},
  {"id": "R02", "tags": ["alpha", "beta"]},
  {"id": "R03", "tags": "comma, inside"},
  {"id": "R04", "tags": ["gamma", "delta, epsilon", "zeta"]}
]

What a naive exporter gets wrong

A naive exporter writes a single tags column. For string values it writes the string as-is. For array values it joins elements with a comma. This creates three measurable problems:

The naive CSV output below shows these problems concretely. R02's array ["alpha","beta"] becomes alpha,beta, which is indistinguishable from a string that happens to contain a comma. R04's array ["gamma","delta, epsilon","zeta"] becomes gamma,delta, epsilon,zeta, where a reader splitting on commas would get four fragments instead of three elements.

id,tags
R01,single
R02,alpha,beta
R03,"comma, inside"
R04,"gamma,delta, epsilon,zeta"

The union-type column policy

Instead of one bare column, expand each union-type field into four columns that together carry the full type information:

SCHEMA = ["id", "tags_kind", "tags_count", "tags_value", "tags_json"]

For a string value, tags_kind is "string", tags_count is 1, tags_value is the raw string, and tags_json is the JSON string literal. For an array value, tags_kind is "array", tags_count is the number of elements, tags_value is the elements joined with a pipe | (chosen because it does not appear in the test data and is unlikely to collide with CSV quoting), and tags_json is the JSON array literal.

The pipe delimiter is a practical choice for this fixture. In production, the delimiter should be declared in a data dictionary or contract alongside the CSV, and the exporter should verify that the chosen delimiter does not appear in any element. If it does, a different delimiter or an escape mechanism is required. The RFC 8259 array grammar allows any mixture of types within a JSON array; the union-type policy makes the string-or-array choice explicit per record.

Measured export results

The exporter was run locally on 2026-10-31 with Python 3.13.14. Source SHA-256: 046bdaac…923d (186 bytes). Output SHA-256: 3b74a04a…8dce (263 bytes). The CSV was read back with Python's csv.DictReader and every field compared against the parsed source.

All four rows passed every check. The CSV contains 4 data rows and 5 columns. The comma inside R03's string value was correctly quoted by the CSV writer. The comma inside R04's second array element (delta, epsilon) was preserved within the pipe-delimited tags_value field and quoted by the CSV writer. The tags_json column contains the exact JSON spelling for each value, enabling a reader to reconstruct the original type and structure without ambiguity.

The exporter code

import json, csv, hashlib, sys
from pathlib import Path

UNION_FIELDS = ["tags"]
BASE_FIELDS = ["id"]
SCHEMA = (
    BASE_FIELDS
    + [f"{f}_kind" for f in UNION_FIELDS]
    + [f"{f}_count" for f in UNION_FIELDS]
    + [f"{f}_value" for f in UNION_FIELDS]
    + [f"{f}_json" for f in UNION_FIELDS]
)
ARRAY_DELIM = "|"

def export_union(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.get("id", "")
        for field in UNION_FIELDS:
            val = rec.get(field, "")
            row[f"{field}_json"] = json.dumps(val, ensure_ascii=False)
            if isinstance(val, str):
                row[f"{field}_kind"] = "string"
                row[f"{field}_count"] = 1
                row[f"{field}_value"] = val
            elif isinstance(val, list):
                row[f"{field}_kind"] = "array"
                row[f"{field}_count"] = len(val)
                row[f"{field}_value"] = ARRAY_DELIM.join(
                    str(e) for e in val)
            else:
                row[f"{field}_kind"] = "other"
                row[f"{field}_count"] = 0
                row[f"{field}_value"] = str(val)
        rows.append(row)

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

The exporter classifies each value before writing. Strings get kind="string" and count=1. Arrays get kind="array" and count=len(array). The tags_json column always stores the exact JSON serialization of the original value, so a downstream reader can parse it back to recover the precise type and structure. The ARRAY_DELIM constant declares the pipe character as the array-element separator; this must be documented alongside the CSV so consumers know how to split tags_value back into elements.

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_kind matches the Python type, tags_count matches the element count, tags_value matches the expected joined or raw value, and tags_json matches the JSON serialization. All four rows passed all four checks.

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

# For each row:
#   expected_kind = "string" if isinstance(src_val, str) else "array"
#   expected_count = 1 if string else len(array)
#   expected_value = val if string else "|".join(array)
#   expected_json = json.dumps(src_val, ensure_ascii=False)
#   assert row["tags_kind"] == expected_kind
#   assert int(row["tags_count"]) == expected_count
#   assert row["tags_value"] == expected_value
#   assert row["tags_json"] == expected_json

Why not just serialize arrays as JSON in one cell?

An alternative approach is to write the JSON serialization of every value into a single tags column: "single" for strings and "[\\"alpha\\", \\"beta\\"]" for arrays. This preserves type information, but it forces every consumer to parse JSON inside a CSV cell, which defeats the purpose of CSV as a simple tabular format. The union-type policy keeps the value column human-readable and CSV-native (pipe-delimited for arrays, raw for strings) while the tags_json column provides an exact fallback for programmatic reconstruction. A consumer that only needs to display the value can read tags_value directly; a consumer that needs the exact structure can parse tags_json.

When the delimiter appears in the data

The pipe delimiter works for this fixture because no element contains a pipe character. In production data this is not guaranteed. The exporter should include a preflight check: if any array element contains the chosen delimiter, the exporter should either select an alternative delimiter that no element contains, or escape occurrences within elements and document the escape rule. This guide does not implement the full escape mechanism; the tags_json column always provides a lossless fallback.

Downloads