{"id":"table-join-count-check","version":"1.0.0","description":"Check a claimed inner, left, right or full equi-join row count over two JSON tables by key multiplicities.","supported_operations":["check a claimed join row count","how many rows will this left join return exactly","verify full outer join result size from two json tables","validate equi-join cardinality with null keys","join fan-out row count check without running the join","does the inner join really produce that many rows","בדיקת מספר שורות בצירוף טבלאות"],"unsupported_operations":["returning the joined rows (use a join tool for small tables)","non-equi joins, cross joins, semi or anti joins, or conditions other than key equality","SQL type casts or collations (keys compare by exact JSON type and code points)","floating-point or object/array key values"],"semantics":["JSON-row profile: a table is an array of JSON objects; a key column that is missing or JSON null is SQL NULL; other fields are ignored.","Key values are strings, booleans or safe integers (|n| <= 2^53 - 1); other numbers, objects and arrays are unsupported_input. Different JSON types never match; strings compare by exact code points with no normalization; -0 equals 0.","left_keys and right_keys pair key columns positionally (1-8 each, same length, no repeats). A row with any NULL key component never matches (comparison with NULL is unknown) and is an unmatched row.","matched_rows = sum over keys k of L(k) * R(k); left_unmatched_rows and right_unmatched_rows count rows whose key is NULL or absent from the other table.","row_count = matched_rows (inner), + left_unmatched_rows (left), + right_unmatched_rows (right), + both (full). The join is never materialized.","valid when claimed_row_count = row_count; else reason row_count_mismatch with first_discrepancy {field: row_count, claimed, actual}. The decomposition and reference_version are always returned."],"limits":{"max_rows_per_table":10000,"max_key_columns":8,"max_column_name_chars":256,"max_key_string_chars":1024},"pricing":{"status":"unpriced","charge_usd":null},"input_schema":{"type":"object","additionalProperties":false,"required":["left","right","left_keys","right_keys","join_type","claimed_row_count"],"properties":{"left":{"type":"array","maxItems":10000,"items":{"type":"object","additionalProperties":{"description":"any JSON value"}}},"right":{"type":"array","maxItems":10000,"items":{"type":"object","additionalProperties":{"description":"any JSON value"}}},"left_keys":{"type":"array","minItems":1,"maxItems":8,"items":{"type":"string","minLength":1,"maxLength":256}},"right_keys":{"type":"array","minItems":1,"maxItems":8,"items":{"type":"string","minLength":1,"maxLength":256}},"join_type":{"type":"string","enum":["inner","left","right","full"]},"claimed_row_count":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"}}},"output_schema":{"type":"object","additionalProperties":false,"required":["valid","reason","first_discrepancy","row_count","matched_rows","left_unmatched_rows","right_unmatched_rows","reference_version"],"properties":{"valid":{"type":"boolean"},"reason":{"type":["string","null"],"enum":["row_count_mismatch",null]},"first_discrepancy":{"type":["object","null"],"additionalProperties":false,"required":["field","claimed","actual"],"properties":{"field":{"type":"string","enum":["row_count"]},"claimed":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"actual":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"}}},"row_count":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"matched_rows":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"left_unmatched_rows":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"right_unmatched_rows":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"reference_version":{"type":"string","const":"r59-join-count-1.0.0 (ISO/IEC 9075-2:2023 joined table and null comparison semantics; JSON-row profile 1)"}}},"examples":[{"input":{"left":[{"num":1,"name":"a"},{"num":2,"name":"b"},{"num":3,"name":"c"}],"right":[{"num":1,"value":"xxx"},{"num":3,"value":"yyy"},{"num":5,"value":"zzz"}],"left_keys":["num"],"right_keys":["num"],"join_type":"full","claimed_row_count":"4"},"output":{"valid":true,"reason":null,"first_discrepancy":null,"row_count":"4","matched_rows":"2","left_unmatched_rows":"1","right_unmatched_rows":"1","reference_version":"r59-join-count-1.0.0 (ISO/IEC 9075-2:2023 joined table and null comparison semantics; JSON-row profile 1)"}},{"input":{"left":[{"k":7},{"k":7},{"k":null}],"right":[{"k":7},{"k":7},{"k":null}],"left_keys":["k"],"right_keys":["k"],"join_type":"left","claimed_row_count":"3"},"output":{"valid":false,"reason":"row_count_mismatch","first_discrepancy":{"field":"row_count","claimed":"3","actual":"5"},"row_count":"5","matched_rows":"4","left_unmatched_rows":"1","right_unmatched_rows":"1","reference_version":"r59-join-count-1.0.0 (ISO/IEC 9075-2:2023 joined table and null comparison semantics; JSON-row profile 1)"}}],"execute_url":"/v1/tools/table-join-count-check/versions/1.0.0/execute"}