Tables
table group_bytable pivottable rolluptable cubetable windowtable toptable sorttable wheretable selecttable distincttable jointable uniontable unpivottable difftable diff
table group_by <json_rows> <json_keys> <json_aggregations>
Description
Group the rows by keys and compute aggregations
Group the rows by keys and compute aggregations
Parameters
json_rows: The rows - String - required
json_keys: The keys, ex: ["shop"] - String - required
json_aggregations: The aggregations - String - required
json_rows: The rows - String - required
json_keys: The keys, ex: ["shop"] - String - required
json_aggregations: The aggregations - String - required
admin
table group_by "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "[\"shop\"]" "{\"total\": [\"sum\", \"amount\"], \"lines\": [\"count\"], \"avg_qty\": [\"avg\", \"qty\"]}"
mentdb
[{"shop": "Paris", "total": 515.5, "lines": 3, "avg_qty": 14.333333333333334}, {"shop": "Lyon", "total": 530, "lines": 3, "avg_qty": 14.666666666666666}]
By shop and product, median and 90th percentile
admin
table group_by "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "[\"shop\", \"product\"]" "{\"median\": [\"median\", \"amount\"], \"p90\": [\"p90\", \"amount\"]}"
mentdb
[{"shop": "Paris", "product": "tea", "median": 107.75, "p90": 117.95}, {"shop": "Paris", "product": "coffee", "median": 300, "p90": 300}, {"shop": "Lyon", "product": "coffee", "median": 235, "p90": 255}, {"shop": "Lyon", "product": "tea", "median": 60, "p90": 60}]
Used in use cases (1 recipe)
Integrator & ETL developer › From a REST API answer to an Excel report: nested orders flattened into lines
Integrator & ETL developer › From a REST API answer to an Excel report: nested orders flattened into lines
Often used with
equal · excelx read · excelx sheets · excelx write · file delete · file exist · file mkdir · json to_rows
equal · excelx read · excelx sheets · excelx write · file delete · file exist · file mkdir · json to_rows
table pivot <json_rows> <row_key> <col_key> <json_aggregation>
Description
Pivot table: one row per value of row_key, one column per value of col_key, the aggregation in the cells and a total column
Pivot table: one row per value of row_key, one column per value of col_key, the aggregation in the cells and a total column
Parameters
json_rows: The rows - String - required
row_key: The field of the rows - String - required
col_key: The field of the columns - String - required
json_aggregation: ["function", "field"] - String - required
json_rows: The rows - String - required
row_key: The field of the rows - String - required
col_key: The field of the columns - String - required
json_aggregation: ["function", "field"] - String - required
admin
table pivot "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "shop" "month" "[\"sum\", \"amount\"]"
mentdb
[{"shop": "Paris", "1": 420.5, "2": 95, "total": 515.5}, {"shop": "Lyon", "1": 210, "2": 320, "total": 530}]
table rollup <json_rows> <json_keys> <json_aggregations>
Description
Group by with subtotals for each level of the keys and a grand total (null in the totalized keys)
Group by with subtotals for each level of the keys and a grand total (null in the totalized keys)
Parameters
json_rows: The rows - String - required
json_keys: The keys, from the widest - String - required
json_aggregations: The aggregations - String - required
json_rows: The rows - String - required
json_keys: The keys, from the widest - String - required
json_aggregations: The aggregations - String - required
admin
table rollup "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "[\"shop\", \"product\"]" "{\"total\": [\"sum\", \"amount\"]}"
mentdb
[{"shop": "Paris", "product": "tea", "total": 215.5}, {"shop": "Paris", "product": "coffee", "total": 300}, {"shop": "Lyon", "product": "coffee", "total": 470}, {"shop": "Lyon", "product": "tea", "total": 60}, {"shop": "Paris", "product": null, "total": 515.5}, {"shop": "Lyon", "product": null, "total": 530}, {"shop": null, "product": null, "total": 1045.5}]
table cube <json_rows> <json_keys> <json_aggregations>
Description
Group by every combination of the keys (subtotals in all the directions, 6 keys at most)
Group by every combination of the keys (subtotals in all the directions, 6 keys at most)
Parameters
json_rows: The rows - String - required
json_keys: The keys - String - required
json_aggregations: The aggregations - String - required
json_rows: The rows - String - required
json_keys: The keys - String - required
json_aggregations: The aggregations - String - required
admin
table cube "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "[\"shop\", \"product\"]" "{\"qty\": [\"sum\", \"qty\"]}"
mentdb
[{"shop": "Paris", "product": "tea", "qty": 18}, {"shop": "Paris", "product": "coffee", "qty": 25}, {"shop": "Lyon", "product": "coffee", "qty": 39}, {"shop": "Lyon", "product": "tea", "qty": 5}, {"shop": "Paris", "product": null, "qty": 43}, {"shop": "Lyon", "product": null, "qty": 44}, {"shop": null, "product": "tea", "qty": 23}, {"shop": null, "product": "coffee", "qty": 64}, {"shop": null, "product": null, "qty": 87}]
table window <json_rows> <json_window>
Description
Window functions: partition, order, then for each row row_number, rank and dense_rank (of the value of the field in the partition, the largest = 1, ["rank", "field", "asc"]: the smallest = 1), cumsum, lag, lead (with an offset), diff, pct_change, moving_avg (with a size), percent_of_total
Window functions: partition, order, then for each row row_number, rank and dense_rank (of the value of the field in the partition, the largest = 1, ["rank", "field", "asc"]: the smallest = 1), cumsum, lag, lead (with an offset), diff, pct_change, moving_avg (with a size), percent_of_total
Parameters
json_rows: The rows - String - required
json_window: {"partition": [...], "order": [...], "functions": {"name": ["function", "field", arg]}} - String - required
json_rows: The rows - String - required
json_window: {"partition": [...], "order": [...], "functions": {"name": ["function", "field", arg]}} - String - required
admin
table window "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "{\"partition\": [\"shop\"], \"order\": [\"month\"], \"functions\": {\"running\": [\"cumsum\", \"amount\"], \"rank\": [\"rank\", \"amount\"], \"share\": [\"percent_of_total\", \"amount\"]}}"
mentdb
[{"shop": "Paris", "month": 1, "product": "tea", "amount": 120.5, "qty": 10, "running": 120.5, "rank": 2, "share": 23.375363724539284}, {"shop": "Paris", "month": 1, "product": "coffee", "amount": 300, "qty": 25, "running": 420.5, "rank": 1, "share": 58.19592628516004}, {"shop": "Paris", "month": 2, "product": "tea", "amount": 95, "qty": 8, "running": 515.5, "rank": 3, "share": 18.42870999030068}, {"shop": "Lyon", "month": 1, "product": "coffee", "amount": 210, "qty": 18, "running": 210, "rank": 2, "share": 39.62264150943396}, {"shop": "Lyon", "month": 2, "product": "coffee", "amount": 260, "qty": 21, "running": 470, "rank": 1, "share": 49.056603773584904}, {"shop": "Lyon", "month": 2, "product": "tea", "amount": 60, "qty": 5, "running": 530, "rank": 3, "share": 11.320754716981131}]
Used in use cases (2 recipes)
Data scientist › A monthly report without a database
Accountant & manager › Invoice total, VAT, amount in words and due date
Data scientist › A monthly report without a database
Accountant & manager › Invoice total, VAT, amount in words and due date
Often used with
date add_business_days_fr · math amount_in_words · math vat · table pivot · table top
date add_business_days_fr · math amount_in_words · math vat · table pivot · table top
table top <json_rows> <json_keys> <json_order> <n>
Description
The n first rows of each group after a sort (top N per group)
The n first rows of each group after a sort (top N per group)
Parameters
json_rows: The rows - String - required
json_keys: The keys of the groups ([] = all the rows) - String - required
json_order: The order: [["field", "desc"], ...] - String - required
n: The number of rows per group - Number - required
json_rows: The rows - String - required
json_keys: The keys of the groups ([] = all the rows) - String - required
json_order: The order: [["field", "desc"], ...] - String - required
n: The number of rows per group - Number - required
admin
table top "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "[\"shop\"]" "[[\"amount\", \"desc\"]]" 1
mentdb
[{"shop": "Paris", "month": 1, "product": "coffee", "amount": 300, "qty": 25}, {"shop": "Lyon", "month": 2, "product": "coffee", "amount": 260, "qty": 21}]
table sort <json_rows> <json_order>
Description
Sort the rows on one or several fields (numbers compared as numbers, null last)
Sort the rows on one or several fields (numbers compared as numbers, null last)
Parameters
json_rows: The rows - String - required
json_order: ["field", ...] or [["field", "asc|desc"], ...] - String - required
json_rows: The rows - String - required
json_order: ["field", ...] or [["field", "asc|desc"], ...] - String - required
admin
table sort "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "[[\"shop\", \"asc\"], [\"amount\", \"desc\"]]"
mentdb
[{"shop": "Lyon", "month": 2, "product": "coffee", "amount": 260, "qty": 21}, {"shop": "Lyon", "month": 1, "product": "coffee", "amount": 210, "qty": 18}, {"shop": "Lyon", "month": 2, "product": "tea", "amount": 60, "qty": 5}, {"shop": "Paris", "month": 1, "product": "coffee", "amount": 300, "qty": 25}, {"shop": "Paris", "month": 1, "product": "tea", "amount": 120.5, "qty": 10}, {"shop": "Paris", "month": 2, "product": "tea", "amount": 95, "qty": 8}]
table where <json_rows> <json_conditions>
Description
Filter the rows: {"field": value} or {"field": {"operator": value}} with eq, ne, gt, ge, lt, le, in, not_in, contains, starts_with, null (true or false); all the conditions must be true
Filter the rows: {"field": value} or {"field": {"operator": value}} with eq, ne, gt, ge, lt, le, in, not_in, contains, starts_with, null (true or false); all the conditions must be true
Parameters
json_rows: The rows - String - required
json_conditions: The conditions - String - required
json_rows: The rows - String - required
json_conditions: The conditions - String - required
admin
table where "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "{\"product\": \"coffee\", \"amount\": {\"gt\": 220}}"
mentdb
[{"shop": "Paris", "month": 1, "product": "coffee", "amount": 300, "qty": 25}, {"shop": "Lyon", "month": 2, "product": "coffee", "amount": 260, "qty": 21}]
table select <json_rows> <json_fields>
Description
Keep some fields, in this order
Keep some fields, in this order
Parameters
json_rows: The rows - String - required
json_fields: The fields - String - required
json_rows: The rows - String - required
json_fields: The fields - String - required
admin
table select "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "[\"product\", \"amount\"]"
mentdb
[{"product": "tea", "amount": 120.5}, {"product": "coffee", "amount": 300}, {"product": "tea", "amount": 95}, {"product": "coffee", "amount": 210}, {"product": "coffee", "amount": 260}, {"product": "tea", "amount": 60}]
table distinct <json_rows> <json_fields>
Description
The distinct combinations of some fields with their count
The distinct combinations of some fields with their count
Parameters
json_rows: The rows - String - required
json_fields: The fields - String - required
json_rows: The rows - String - required
json_fields: The fields - String - required
admin
table distinct "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "[\"shop\", \"product\"]"
mentdb
[{"shop": "Paris", "product": "tea", "count": 2}, {"shop": "Paris", "product": "coffee", "count": 1}, {"shop": "Lyon", "product": "coffee", "count": 2}, {"shop": "Lyon", "product": "tea", "count": 1}]
table join <json_left> <json_right> <on> <type>
Description
Join two tables: inner, left, right or full; on = a field, a list of fields or {"left": ..., "right": ...}; a field of the right table that already exists gets the suffix _right
Join two tables: inner, left, right or full; on = a field, a list of fields or {"left": ..., "right": ...}; a field of the right table that already exists gets the suffix _right
Parameters
json_left: The left rows - String - required
json_right: The right rows - String - required
on: The join keys - String - required
type: inner, left, right or full - String - required
json_left: The left rows - String - required
json_right: The right rows - String - required
on: The join keys - String - required
type: inner, left, right or full - String - required
admin
table join "[{\"shop\": \"Paris\", \"month\": 1, \"product\": \"tea\", \"amount\": 120.5, \"qty\": 10}, {\"shop\": \"Paris\", \"month\": 1, \"product\": \"coffee\", \"amount\": 300, \"qty\": 25}, {\"shop\": \"Paris\", \"month\": 2, \"product\": \"tea\", \"amount\": 95, \"qty\": 8}, {\"shop\": \"Lyon\", \"month\": 1, \"product\": \"coffee\", \"amount\": 210, \"qty\": 18}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"coffee\", \"amount\": 260, \"qty\": 21}, {\"shop\": \"Lyon\", \"month\": 2, \"product\": \"tea\", \"amount\": 60, \"qty\": 5}]" "[{\"shop\": \"Paris\", \"region\": \"IDF\", \"surface\": 120}, {\"shop\": \"Lyon\", \"region\": \"ARA\", \"surface\": 90}, {\"shop\": \"Nice\", \"region\": \"PACA\", \"surface\": 70}]" "shop" "left"
mentdb
[{"shop": "Paris", "month": 1, "product": "tea", "amount": 120.5, "qty": 10, "region": "IDF", "surface": 120}, {"shop": "Paris", "month": 1, "product": "coffee", "amount": 300, "qty": 25, "region": "IDF", "surface": 120}, {"shop": "Paris", "month": 2, "product": "tea", "amount": 95, "qty": 8, "region": "IDF", "surface": 120}, {"shop": "Lyon", "month": 1, "product": "coffee", "amount": 210, "qty": 18, "region": "ARA", "surface": 90}, {"shop": "Lyon", "month": 2, "product": "coffee", "amount": 260, "qty": 21, "region": "ARA", "surface": 90}, {"shop": "Lyon", "month": 2, "product": "tea", "amount": 60, "qty": 5, "region": "ARA", "surface": 90}]
Shops without sales (full join)
admin
table join "[{\"shop\": \"Paris\", \"region\": \"IDF\", \"surface\": 120}, {\"shop\": \"Lyon\", \"region\": \"ARA\", \"surface\": 90}, {\"shop\": \"Nice\", \"region\": \"PACA\", \"surface\": 70}]" "[{\"shop\": \"Paris\", \"amount\": 515.5}, {\"shop\": \"Lyon\", \"amount\": 530}]" "[\"shop\"]" "full"
mentdb
[{"shop": "Paris", "region": "IDF", "surface": 120, "amount": 515.5}, {"shop": "Lyon", "region": "ARA", "surface": 90, "amount": 530}, {"shop": "Nice", "region": "PACA", "surface": 70, "amount": null}]
table union <json_rows_a> <json_rows_b>
Description
Rows of two tables one after the other (the missing fields are null)
Rows of two tables one after the other (the missing fields are null)
Parameters
json_rows_a: The first rows - String - required
json_rows_b: The second rows - String - required
json_rows_a: The first rows - String - required
json_rows_b: The second rows - String - required
admin
table union "[{\"id\": 1, \"v\": 10}]" "[{\"id\": 2, \"w\": 5}]"
mentdb
[{"id": 1, "v": 10, "w": null}, {"id": 2, "v": null, "w": 5}]
table unpivot <json_rows> <json_keep> <name_field> <value_field>
Description
Columns to rows: the kept fields, then one row per other field with its name and its value (melt)
Columns to rows: the kept fields, then one row per other field with its name and its value (melt)
Parameters
json_rows: The rows - String - required
json_keep: The kept fields - String - required
name_field: The name of the field that receives the column names - String - required
value_field: The name of the field that receives the values - String - required
json_rows: The rows - String - required
json_keep: The kept fields - String - required
name_field: The name of the field that receives the column names - String - required
value_field: The name of the field that receives the values - String - required
admin
table unpivot "[{\"station\": \"S1\", \"jan\": 12.1, \"feb\": 13.4}, {\"station\": \"S2\", \"jan\": 9.8, \"feb\": 10.2}]" "[\"station\"]" "month" "temperature"
mentdb
[{"station": "S1", "month": "jan", "temperature": 12.1}, {"station": "S1", "month": "feb", "temperature": 13.4}, {"station": "S2", "month": "jan", "temperature": 9.8}, {"station": "S2", "month": "feb", "temperature": 10.2}]
table diff <json_rows_before> <json_rows_after> <json_keys>
Description
Compare two versions of a table by key: added rows, deleted rows, changed rows (key, fields before / after, names of the changed fields) and the number of unchanged rows. Hash join: 500 000 rows on each side in about 1.5 s
Compare two versions of a table by key: added rows, deleted rows, changed rows (key, fields before / after, names of the changed fields) and the number of unchanged rows. Hash join: 500 000 rows on each side in about 1.5 s
Parameters
json_rows_before: The rows before (array of objects) - String - required
json_rows_after: The rows after - String - required
json_keys: The key columns, ex: ["id"] - String - required
json_rows_before: The rows before (array of objects) - String - required
json_rows_after: The rows after - String - required
json_keys: The key columns, ex: ["id"] - String - required
admin
table diff "[{\"id\": 1, \"name\": \"Dupont SA\", \"city\": \"Saint-Denis\", \"balance\": 120.5}, {\"id\": 2, \"name\": \"Hoarau\", \"city\": \"Saint-Denis\", \"balance\": 0}, {\"id\": 3, \"name\": \"Payet\", \"city\": \"Le Port\", \"balance\": 30}]" "[{\"id\": 1, \"name\": \"Dupont SA \", \"city\": \"Saint-Denis\", \"balance\": \"120.50\"}, {\"id\": 2, \"name\": \"Hoarau\", \"city\": \"Saint-Pierre\", \"balance\": 0}, {\"id\": 4, \"name\": \"Grondin\", \"city\": \"Cilaos\", \"balance\": 10}]" "[\"id\"]"
mentdb
{"added": [{"id": 4, "name": "Grondin", "city": "Cilaos", "balance": 10}], "deleted": [{"id": 3, "name": "Payet", "city": "Le Port", "balance": 30}], "changed": [{"key": {"id": 2}, "before": {"city": "Saint-Denis"}, "after": {"city": "Saint-Pierre"}, "fields": ["city"]}], "unchanged": 1, "counts": {"before": 3, "after": 3, "added": 1, "deleted": 1, "changed": 1, "unchanged": 1}}
table diff <json_rows_before> <json_rows_after> <json_keys> <json_params>
Description
Compare with parameters: output (full, counts, upsert: the rows to upsert = added + changed, and delete_keys), compare (the columns to compare, default all but the keys), ignore (columns not compared), numeric_tolerance (0), case_insensitive (false), trim (true), null_equals_empty (true), loose_types (true: 120.5 = "120.5", "true" = true). A key missing or given twice is an error case_insensitive compares the values without case, the keys stay case sensitive unless case_insensitive_keys (false). With loose_types, true and "TRUE" are equal; a number with an exponent beyond 1000 (1e500000000) is compared as text.
Compare with parameters: output (full, counts, upsert: the rows to upsert = added + changed, and delete_keys), compare (the columns to compare, default all but the keys), ignore (columns not compared), numeric_tolerance (0), case_insensitive (false), trim (true), null_equals_empty (true), loose_types (true: 120.5 = "120.5", "true" = true). A key missing or given twice is an error case_insensitive compares the values without case, the keys stay case sensitive unless case_insensitive_keys (false). With loose_types, true and "TRUE" are equal; a number with an exponent beyond 1000 (1e500000000) is compared as text.
Parameters
json_rows_before: The rows before - String - required
json_rows_after: The rows after - String - required
json_keys: The key columns - String - required
json_params: The parameters - String - required
json_rows_before: The rows before - String - required
json_rows_after: The rows after - String - required
json_keys: The key columns - String - required
json_params: The parameters - String - required
admin
table diff "[{\"id\": 1, \"name\": \"Dupont SA\", \"city\": \"Saint-Denis\", \"balance\": 120.5}, {\"id\": 2, \"name\": \"Hoarau\", \"city\": \"Saint-Denis\", \"balance\": 0}, {\"id\": 3, \"name\": \"Payet\", \"city\": \"Le Port\", \"balance\": 30}]" "[{\"id\": 1, \"name\": \"Dupont SA \", \"city\": \"Saint-Denis\", \"balance\": \"120.50\"}, {\"id\": 2, \"name\": \"Hoarau\", \"city\": \"Saint-Pierre\", \"balance\": 0}, {\"id\": 4, \"name\": \"Grondin\", \"city\": \"Cilaos\", \"balance\": 10}]" "[\"id\"]" "{\"output\": \"upsert\"}"
mentdb
{"upsert": [{"id": 2, "name": "Hoarau", "city": "Saint-Pierre", "balance": 0}, {"id": 4, "name": "Grondin", "city": "Cilaos", "balance": 10}], "delete_keys": [{"id": 3}], "counts": {"before": 3, "after": 3, "added": 1, "deleted": 1, "changed": 1, "unchanged": 1}}
Only the counts, the city is not compared
admin
table diff "[{\"id\": 1, \"name\": \"Dupont SA\", \"city\": \"Saint-Denis\", \"balance\": 120.5}, {\"id\": 2, \"name\": \"Hoarau\", \"city\": \"Saint-Denis\", \"balance\": 0}, {\"id\": 3, \"name\": \"Payet\", \"city\": \"Le Port\", \"balance\": 30}]" "[{\"id\": 1, \"name\": \"Dupont SA \", \"city\": \"Saint-Denis\", \"balance\": \"120.50\"}, {\"id\": 2, \"name\": \"Hoarau\", \"city\": \"Saint-Pierre\", \"balance\": 0}, {\"id\": 4, \"name\": \"Grondin\", \"city\": \"Cilaos\", \"balance\": 10}]" "[\"id\"]" "{\"output\": \"counts\", \"ignore\": [\"city\"]}"
mentdb
{"before": 3, "after": 3, "added": 1, "deleted": 1, "changed": 0, "unchanged": 2}
Used in use cases (1 recipe)
Integrator & ETL developer › Synchronise a table incrementally: create it from the data, compare two extracts, apply only the changes
Integrator & ETL developer › Synchronise a table incrementally: create it from the data, compare two extracts, apply only the changes
Often used with
sql bulk_insert · sql bulk_upsert · sql connect · sql create_table · sql disconnect · sql dml · sql encode · sql value
sql bulk_insert · sql bulk_upsert · sql connect · sql create_table · sql disconnect · sql dml · sql encode · sql value