playbooks

home / skills / bdambrosio / cognitive_workbench / test-json-sql-join

test-json-sql-join skill

/src/saved_plans/test-json-sql-join

This skill helps you test SQL inner joins by generating in memory datasets and validating expected joined results.

npx playbooks add skill bdambrosio/cognitive_workbench --skill test-json-sql-join

Review the files below or copy the command above to add this skill to your agents.

Files (2)
SKILL.md
427 B
---
name: test-json-sql-join
description: Tests join primitive (INNER JOIN)
manual_only: true
---

# Test Join Primitive

**Self-contained:** Creates test data internally

**Input:** 
- Creates $papers: A, B, C, D
- Creates $authors: A (Alice), B (Bob), E (Eve)

**Operation:** Inner join on id field

**Expected Output:** $joined collection with 2 items (A and B have matches)
- Paper A + Author Alice
- Paper B + Author Bob

Overview

This skill tests an INNER JOIN primitive by creating self-contained sample data and asserting the expected join results. It validates that only records with matching id values from two collections are returned. The test is focused, deterministic, and requires no external dependencies.

How this skill works

The skill constructs two in-memory collections: papers and authors. Papers include items A, B, C, D and authors include A (Alice), B (Bob), E (Eve). It performs an inner join on the id field and produces a joined collection containing only records with matching ids (A and B). The output is asserted to contain exactly two joined items combining paper and author data.

When to use it

  • Verify INNER JOIN behavior in a data transformation or query engine.
  • Add a regression test for join logic after refactoring join code paths.
  • Validate mapping of related records when preparing merge or enrichment operations.
  • Teach or demonstrate basic join semantics with concrete examples.
  • Confirm that unmatched records are excluded from results.

Best practices

  • Keep test data minimal and explicit to make failures easy to interpret.
  • Assert both the count of joined items and the shape/content of each joined record.
  • Include distinct unmatched records to ensure exclusion behavior is validated.
  • Use stable, deterministic identifiers rather than generated values to avoid flakiness.
  • Run this test as part of a fast unit test suite to catch join regressions early.

Example use cases

  • Unit test for a SQL abstraction layer implementing INNER JOIN semantics.
  • Validation of a custom in-memory join function used in ETL pipelines.
  • Educational example showing how join keys determine matched results.
  • Quick check after changing key normalization or comparison logic.
  • Integration smoke test when wiring a new database adapter.

FAQ

What does the test assert?

It asserts that the inner join returns exactly two joined items combining paper A with author Alice and paper B with author Bob.

Why are papers C, D and author E excluded?

They do not have matching id values across the two collections, so an INNER JOIN excludes them by design.