Of course it can be done in Python. It seems like it could be done in bash too.
Convert JSON → CSV with an RFC-6901 pointer per row, then left-join your template to add Label/section/subsection. Below is a revised, template-aware solution plus a jq one-liner. Arrays become multiple rows with an index column. This keeps reads/writes unambiguous and scalable to 1,200+ fields.
Background that matters
- Pointer is your stable key. RFC-6901 defines how to address any JSON value and how to escape
~→~0 and /→~1. Use it as pointerforjson. (datatracker.ietf.org)
- Edits stay safe. If you later write back, use RFC-6902 JSON Patch ops against those pointers. (datatracker.ietf.org)
- CLI and libs exist.
jq can walk scalars and emit CSV; Python has python-json-pointer for spec-accurate pointers and ijson for streaming large files; Node has json2csv if you’re in JS. (jqlang.org)
Option A — jq (quick, zero code)
Produces rows with pointerforjson, property value, repeatable, array index, propertyname. Join this with your template on pointerforjson.
# jq manual: https://jqlang.org/manual/ # paths, getpath, @csv
# RFC 6901: https://datatracker.ietf.org/doc/html/rfc6901 # escaping rules
jq -r '
def esc: gsub("~";"~0") | gsub("/";"~1"); # RFC 6901 escape
def ptr($p): "/" + ($p | map(tostring | esc) | join("/"));
# header matching your schema
("Label,propertyname,repeatable,array/listindex,property value,section,subsection,pointerforjson"),
# one row per scalar
( paths(scalars) as $p
| [
"", # Label (filled by template join)
([$p[]|tostring] | last(select(test("^[^0-9]+$"))) // ""),
(any($p[]; type=="number")), # repeatable
((first($p[]|select(type=="number"))) // ""), # array/listindex
(getpath($p)), # property value
"", "", # section, subsection (from template)
ptr($p) # pointerforjson
] | @csv
)
' input.json > raw_extract.csv
jq’s paths(scalars) and getpath are stable primitives; @csv writes valid CSV. (jqlang.org)
Option B — Python (template-aware, spec-accurate, extendable)
Reads JSON, emits the exact CSV you asked for, and inlines a left-join with your template (keyed by pointer), so ambiguous keys like desc map to your canonical Label/section/subsection.
#!/usr/bin/env python3
# JSON→CSV with RFC 6901 pointers + template join
# RFC 6901: https://datatracker.ietf.org/doc/html/rfc6901
# python-json-pointer docs: https://python-json-pointer.readthedocs.io/
# ijson (streaming, optional): https://pypi.org/project/ijson/
import json, csv, sys
from typing import Any, List, Dict
def esc_token(s: str) -> str: # RFC 6901 escaping
return s.replace("~", "~0").replace("/", "~1")
def walk(doc: Any, base: List[str] = []):
if isinstance(doc, dict):
for k, v in doc.items():
yield from walk(v, base + [k])
elif isinstance(doc, list):
for i, v in enumerate(doc):
yield from walk(v, base + [str(i)])
else:
yield base, doc # scalar
def load_template(path: str) -> Dict[str, Dict[str,str]]:
"""
Template CSV must have at least: pointer,label,section,subsection
pointer must be RFC 6901 (same as we emit here)
"""
m = {}
with open(path, newline="", encoding="utf-8") as f:
r = csv.DictReader(f)
for row in r:
m[row["pointer"]] = row
return m
def main(in_json: str, out_csv: str, template_csv: str | None):
template = load_template(template_csv) if template_csv else {}
with open(in_json, "r", encoding="utf-8") as f:
doc = json.load(f)
cols = ["Label","propertyname","repeatable","array/listindex",
"property value","section","subsection","pointerforjson"]
with open(out_csv, "w", newline="", encoding="utf-8") as f:
w = csv.DictWriter(f, fieldnames=cols)
w.writeheader()
for path, val in walk(doc):
ptr = "/" + "/".join(esc_token(p) for p in path)
idxs = [int(p) for p in path if p.isdigit()]
propname = next((p for p in reversed(path) if not p.isdigit()), "")
t = template.get(ptr, {})
w.writerow({
"Label": t.get("label",""),
"propertyname": propname,
"repeatable": bool(idxs),
"array/listindex": idxs[0] if idxs else "",
"property value": val,
"section": t.get("section",""),
"subsection": t.get("subsection",""),
"pointerforjson": ptr
})
if __name__ == "__main__":
# Usage: python json_to_csv.py input.json out.csv template.csv
in_json = sys.argv[1]
out_csv = sys.argv[2] if len(sys.argv)>2 else "out.csv"
template = sys.argv[3] if len(sys.argv)>3 else None
main(in_json, out_csv, template)
Why these choices:
pointerforjson follows RFC-6901, so every CSV row maps back to an exact JSON value. (datatracker.ietf.org)- If your files are huge, swap
json.load for ijson to stream without loading everything in memory. (PyPI)
- If you prefer a JS stack, json2csv provides streaming conversion; pre-walk to add a
pointer field, then feed objects to the parser. Docs and repo are active. (juanjodiaz.github.io)
Notes, tips, pitfalls
- Arrays become multiple rows.
repeatable=true and array/listindex carries the index. That matches your requested schema and keeps edits precise.
- Template is the semantic layer. Keep
pointer,label,section,subsection,aliases in your catalog. Your chatbot retrieves rows by label or alias, then writes back by pointer.
- If you later write back, generate JSON Patch a la
[{op:'replace', path:pointer, value:new}] using the same pointers; this is a standardized format. (datatracker.ietf.org)
- Alternative CLIs. Miller (
mlr) can convert JSON↔CSV fast but you still need to compute pointer strings yourself; docs and GitHub issues show JSON/CSV behavior. (miller.readthedocs.io)