JSON Union → One CSV (heterogeneous keys)¶
What
Merge several JSON exports whose objects carry different key sets into a single CSV with every column, where a key a record never had collapses to an empty cell.
Synthetic / teaching example
The data here is constructed, not sourced
— two CRM dumps (crm_alpha.in.json with vat+email, crm_beta.in.json with
phone). The problem class — JSON omits empty keys, so two dumps of "the same
thing" are structurally different — is universal; the rows are not.
Why interesting¶
Unlike CSV, JSON has no fixed column list: every object only
contains the keys it has a value for. Concatenate two API dumps and you get
ragged objects — vat in one, phone in the other, email in some of both.
Loading them into one table normally means a jq/pandas script to align the
columns. bxp does the alignment from the output_schema alone.
flowchart LR
A["crm_alpha.in.json<br/><small>vat · email</small>"] --> U["union<br/><small>output_schema · combined_output</small>"]
B["crm_beta.in.json<br/><small>phone</small>"] --> U
U --> C["1-json_union-combined.csvx<br/><small>company · vat · email · phone</small>"]Problem class documented in. (sources for the problem class — not for the data)
- The JSON object model has no schema; missing-vs-null is a perennial pain when
flattening arrays of objects (the reason
pandas.json_normalizeandjq -sexist).
The trick¶
(see inline comments in sample.json):
Two mechanisms together — file_type_in: "json" (bxp takes the union of keys
per file as that file's columns; an absent key reads as "") and
combined_output: true (both files run through the one template into a single
1-json_union-combined.csvx). The output_schema lists every target column;
each input normalises to it automatically.
Per-file outputs
bxp also writes a per-file <stem>.csvx; the combined file is the union
result.
Final result¶
Two ragged JSON files become one rectangular CSV — vat filled
only for alpha rows, phone only for beta, email wherever it existed:
company,vat,email,phone
Acme s.r.o.,CZ12345678,sales@acme.cz,
Globex a.s.,CZ99887766,info@globex.cz,
Beta Ltd,,,555-0101
Initech,,hi@initech.io,555-0102
Sample data¶
Run it with bxp-cli --config ./sample.json --template json_union:
{
// Teaching example — synthetic data. Two CRM exports arrive as JSON arrays
// with DIFFERENT key sets: `crm_alpha` has vat + email, `crm_beta` has phone
// (and email only on some records). JSON omits empty keys entirely, so the two
// files are structurally heterogeneous. Goal: one CSV with every column, where
// a key a record never had collapses to "".
//
// Two mechanisms combine here:
// * file_type_in:json — bxp takes the UNION of keys per file as that file's
// columns; a key absent from a record reads as "".
// * combined_output — both files run through this one template into a single
// output, `1-json_union-combined.csvx`. The output_schema below IS the
// unified target schema; each input normalises to it automatically.
conversion_templates: {
json_union: {
data_dir: ".",
file_type_in: "json",
file_pattern_in: ".in.json",
file_type_out: "csv",
file_pattern_out: ".csvx",
combined_output: true,
input_schema: {
$company: "[company]",
$vat: "[vat]", // only in crm_alpha
$email: "[email]", // in crm_alpha, and some crm_beta rows
$phone: "[phone]" // only in crm_beta
},
row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
output_schema: {
company: "$company",
vat: "$vat",
email: "$email",
phone: "$phone"
}
}
}
}