Guides / Column paths

Prevent Flattened JSON Column Name Collisions

The collision happens during naming

These are distinct source fields, not repeated keys inside one object. Joining their path segments with a dot throws away the distinction between a separator and a dot in the original name. A converter may overwrite a value, merge cells or apply its own naming rule; inspect the actual output rather than assuming which behavior it uses.

[{"a.b":"literal dot","a":{"b":"nested"},"a/b":"slash","a~b":"tilde"}]

The example adds slash and tilde keys as well. These characters matter if you choose JSON Pointer as the header syntax. The downloadable map retains the original key segments beside each header, so the naming decision can be audited.

A row-relative JSON Pointer contract

RFC 6901 defines JSON Pointer syntax. Escape a tilde inside a key as ~0 and a slash as ~1, then join escaped segments with slashes. A dot remains part of a key. These headers point inside each selected record, not from the root of the complete array.

/a~1b identifies the literal slash key, not the nested path. /a~0b identifies the literal tilde key. Pointer decoding reverses ~1 first and ~0 second, as explained in RFC 6901 evaluation. Do not percent-decode plain pointer headers as if they were URL fragments.

Download the original mapping example

python pointer-columns-to-csv.py path-collision.json new-output.csv new-map.json

The local execution produced 1 data record and 4 distinct columns. It found one collision under naive dot joining and verified all four string leaf values against parsed CSV cells. The evidence refers only to this fixture. The script does not overwrite existing output names.

Question: Can I fix this by renaming the CSV column afterward?

Only if the distinct values and their source paths survived the original conversion. Once one value has overwritten another, changing a header cannot recover it. Return to the unchanged JSON, create unique headers before assigning cells, and rerun. Keep the column map with the CSV.

Read the exporter

"""String-leaf object example: unambiguous row-relative JSON Pointer headers.
python pointer-columns-to-csv.py path-collision.json new-output.csv new-map.json
"""
import csv,json,sys
from pathlib import Path

def unique_object(pairs):
    result={}
    for key,value in pairs:
        if key in result:raise ValueError('duplicate source key: '+repr(key))
        result[key]=value
    return result

def leaves(obj,path=()):
    if not isinstance(obj,dict) or not obj:raise ValueError('nonempty objects required')
    result={}
    for key,value in obj.items():
        child=path+(key,)
        if isinstance(value,dict):result.update(leaves(value,child))
        elif isinstance(value,str):result[child]=value
        else:raise ValueError('this example supports string leaves only')
    return result

def pointer(path):
    return ''.join('/'+token.replace('~','~0').replace('/','~1') for token in path)

def export(source,target,map_target):
    if Path(target).exists() or Path(map_target).exists():raise FileExistsError('use new filenames')
    values=json.loads(Path(source).read_text(encoding='utf-8'),object_pairs_hook=unique_object)
    if not isinstance(values,list) or not values:raise ValueError('nonempty array required')
    flattened=[leaves(row) for row in values]
    paths=sorted(flattened[0])
    if any(set(row)!=set(paths) for row in flattened):raise ValueError('same leaf paths required in every row')
    headers=[pointer(path) for path in paths]
    if len(headers)!=len(set(headers)):raise ValueError('header collision')
    mapping=[{'header':header,'segments':list(path)} for header,path in zip(headers,paths)]
    with Path(target).open('x',encoding='utf-8',newline='') as file:
        writer=csv.writer(file);writer.writerow(headers)
        writer.writerows([[row[path] for path in paths] for row in flattened])
    Path(map_target).write_text(json.dumps(mapping,ensure_ascii=False,indent=2),encoding='utf-8')
    with Path(target).open(encoding='utf-8',newline='') as file:parsed=list(csv.reader(file))
    expected=[headers]+[[row[path] for path in paths] for row in flattened]
    if parsed!=expected:raise ValueError('parsed CSV differs from source mapping')
    dot_names={};collisions=[]
    for path in paths:
        name='.'.join(path)
        if name in dot_names:collisions.append({'dot_header':name,'first':list(dot_names[name]),'second':list(path)})
        else:dot_names[name]=path
    return {'records':len(parsed)-1,'columns':len(headers),'mapping':mapping,'parsed_csv':parsed,
            'dot_join_collisions':collisions,'all_string_leaf_values_match':True}

if __name__=='__main__':print(json.dumps(export(*sys.argv[1:4]),ensure_ascii=False))

Limits of this deliberate example

It supports a nonempty array of nonempty objects with string leaves, including nested objects. Every record must have the same leaf paths. It rejects nested arrays, empty objects and non-string leaves rather than pretending their representation is settled. It also rejects duplicate source keys before they become dictionaries.

This example loads the small source in memory and does not reconstruct a whole nested document from the CSV. It verifies path-to-cell correspondence. JSON Pointer alone does not encode whether a numeric path token came from an object key or an array index; extending this contract to arrays requires container information or an explicit schema.

Human-friendly aliases also work if you keep an explicit one-to-one map and check for duplicate aliases. Do not assume a CSV recipient understands JSON Pointer automatically. Explain the header convention in the handoff.

For repeated source names, check duplicate JSON keys before parsing. For child arrays, split related parent and child tables. After naming is settled, verify every row and field.

Open the JSON to CSV converter