/**
* postgres-mcp - JSONB Tools Unit Tests
*
* Tests for JSONB basic and advanced operations (19 tools total).
*/
import { describe, it, expect, vi, beforeEach } from "vitest";
import type { PostgresAdapter } from "../../PostgresAdapter.js";
import {
createMockPostgresAdapter,
createMockRequestContext,
} from "../../../../__tests__/mocks/index.js";
import { getJsonbTools } from "../jsonb/index.js";
describe("JSONB Tools", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let mockContext: ReturnType<typeof createMockRequestContext>;
let tools: ReturnType<typeof getJsonbTools>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
mockContext = createMockRequestContext();
tools = getJsonbTools(mockAdapter as unknown as PostgresAdapter);
});
const findTool = (name: string) => tools.find((t) => t.name === name);
describe("pg_jsonb_extract", () => {
it("should extract value using path expression", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ extracted_value: "John" }],
});
const tool = findTool("pg_jsonb_extract");
const result = (await tool!.handler(
{
table: "users",
column: "data",
path: "$.name",
},
mockContext,
)) as { rows: { value: unknown }[] };
expect(result.rows).toEqual([{ value: "John" }]);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("#>"),
expect.anything(),
);
});
it("should handle array paths", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ extracted_value: "item1" }],
});
const tool = findTool("pg_jsonb_extract");
await tool!.handler(
{
table: "orders",
column: "items",
path: "{0,name}",
},
mockContext,
);
expect(mockAdapter.executeQuery).toHaveBeenCalled();
});
});
describe("pg_jsonb_set", () => {
it("should set value at specified path", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rowsAffected: 1 });
const tool = findTool("pg_jsonb_set");
const result = (await tool!.handler(
{
table: "users",
column: "data",
path: ["name"],
value: { first: "Jane" }, // Use object to test JSON stringification
where: "id = 1",
},
mockContext,
)) as { rowsAffected: number };
expect(result.rowsAffected).toBe(1);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_set"),
expect.arrayContaining([["name"], '{"first":"Jane"}', true]),
);
});
});
describe("pg_jsonb_insert", () => {
it("should insert value into JSONB", async () => {
// First call: NULL column check
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ null_count: 0 }],
});
// Second call: array type check
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ type: "array" }],
});
// Third call: actual insert
mockAdapter.executeQuery.mockResolvedValueOnce({ rowsAffected: 1 });
const tool = findTool("pg_jsonb_insert");
const result = (await tool!.handler(
{
table: "users",
column: "data",
path: ["tags", "0"],
value: "new-tag",
where: "id = 1",
},
mockContext,
)) as { rowsAffected: number };
expect(result.rowsAffected).toBe(1);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_insert"),
expect.anything(),
);
});
});
describe("pg_jsonb_delete", () => {
it("should delete key from JSONB", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rowsAffected: 1 });
const tool = findTool("pg_jsonb_delete");
const result = (await tool!.handler(
{
table: "users",
column: "data",
path: "old_key",
where: "id = 1",
},
mockContext,
)) as { rowsAffected: number };
expect(result.rowsAffected).toBe(1);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("- $1"),
["old_key"],
);
});
it("should delete nested path from JSONB", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rowsAffected: 1 });
const tool = findTool("pg_jsonb_delete");
await tool!.handler(
{
table: "users",
column: "data",
path: ["nested", "key"],
where: "id = 1",
},
mockContext,
);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("#- $1"),
[["nested", "key"]],
);
});
});
describe("pg_jsonb_contains", () => {
it("should find rows with containment", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ id: 1, data: { role: "admin" } }],
});
const tool = findTool("pg_jsonb_contains");
const result = (await tool!.handler(
{
table: "users",
column: "data",
value: { role: "admin" },
},
mockContext,
)) as { rows: unknown[]; count: number };
expect(result.count).toBe(1);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("@>"),
[JSON.stringify({ role: "admin" })],
);
});
it("should use specific select columns", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [{ id: 1 }] });
const tool = findTool("pg_jsonb_contains");
await tool!.handler(
{
table: "users",
column: "data",
value: { active: true },
select: ["id", "name"],
},
mockContext,
);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining('"id", "name"'),
expect.anything(),
);
});
});
describe("pg_jsonb_path_query", () => {
it("should query using SQL/JSON path", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ result: "value1" }, { result: "value2" }],
});
const tool = findTool("pg_jsonb_path_query");
const result = (await tool!.handler(
{
table: "documents",
column: "content",
path: "$.items[*].name",
},
mockContext,
)) as { results: unknown[] };
expect(result.results).toHaveLength(2);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_path_query"),
expect.anything(),
);
});
it("should pass variables to path query", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = findTool("pg_jsonb_path_query");
await tool!.handler(
{
table: "documents",
column: "content",
path: "$.items[*] ? (@.price > $min)",
vars: { min: 10 },
},
mockContext,
);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.anything(),
expect.arrayContaining([expect.stringContaining("min")]),
);
});
});
describe("pg_jsonb_agg", () => {
it("should aggregate rows into JSONB array", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ result: [{ id: 1 }, { id: 2 }] }],
});
const tool = findTool("pg_jsonb_agg");
const result = (await tool!.handler(
{
table: "users",
},
mockContext,
)) as { result: unknown[] };
expect(result.result).toHaveLength(2);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_agg"),
);
});
it("should select specific columns", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ result: [{}] }],
});
const tool = findTool("pg_jsonb_agg");
await tool!.handler(
{
table: "users",
select: ["id", "name"],
},
mockContext,
);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_build_object"),
);
});
});
describe("pg_jsonb_object", () => {
it("should build JSONB object from key-value pairs", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ result: { name: "John", age: 30 } }],
});
const tool = findTool("pg_jsonb_object");
// Use 'data' parameter to pass key-value pairs (MCP tool format)
const result = (await tool!.handler(
{
data: { name: "John", age: 30 },
},
mockContext,
)) as { object: Record<string, unknown> };
expect(result).toEqual({ object: { name: "John", age: 30 } });
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_build_object"),
expect.anything(),
);
});
});
describe("pg_jsonb_array", () => {
it("should build JSONB array from values", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ result: [1, 2, 3] }],
});
const tool = findTool("pg_jsonb_array");
const result = (await tool!.handler(
{
values: [1, 2, 3],
},
mockContext,
)) as { array: number[] };
expect(result.array).toEqual([1, 2, 3]);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_build_array"),
expect.anything(),
);
});
});
describe("pg_jsonb_keys", () => {
it("should get all keys from JSONB", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ key: "name" }, { key: "email" }, { key: "age" }],
});
const tool = findTool("pg_jsonb_keys");
const result = (await tool!.handler(
{
table: "users",
column: "data",
},
mockContext,
)) as { keys: string[] };
expect(result.keys).toEqual(["name", "email", "age"]);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_object_keys"),
);
});
});
describe("pg_jsonb_strip_nulls", () => {
it("should remove null values from JSONB", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rowsAffected: 5 });
const tool = findTool("pg_jsonb_strip_nulls");
const result = (await tool!.handler(
{
table: "users",
column: "data",
where: "id > 0",
},
mockContext,
)) as { rowsAffected: number };
expect(result.rowsAffected).toBe(5);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_strip_nulls"),
);
});
});
describe("pg_jsonb_typeof", () => {
it("should get type of JSONB values", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ type: "object" }, { type: "array" }],
});
const tool = findTool("pg_jsonb_typeof");
const result = (await tool!.handler(
{
table: "data",
column: "content",
},
mockContext,
)) as { types: string[] };
expect(result.types).toEqual(["object", "array"]);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_typeof"),
[],
);
});
it("should check type at specific path", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ type: "string" }],
});
const tool = findTool("pg_jsonb_typeof");
await tool!.handler(
{
table: "data",
column: "content",
path: ["nested", "field"],
},
mockContext,
);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("#>"),
[["nested", "field"]],
);
});
});
// Advanced JSONB Tools
describe("pg_jsonb_validate_path", () => {
it("should validate valid JSONPath", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ path: "$.items[*].name" }],
});
const tool = findTool("pg_jsonb_validate_path");
const result = (await tool!.handler(
{
path: "$.items[*].name",
},
mockContext,
)) as { valid: boolean };
expect(result.valid).toBe(true);
});
it("should test path against value", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ result: "apple" }, { result: "banana" }],
});
const tool = findTool("pg_jsonb_validate_path");
const result = (await tool!.handler(
{
path: "$.items[*]",
testValue: { items: ["apple", "banana"] },
},
mockContext,
)) as { valid: boolean; results: string[] };
expect(result.valid).toBe(true);
expect(result.results).toHaveLength(2);
});
it("should return invalid for bad path", async () => {
mockAdapter.executeQuery.mockRejectedValueOnce(new Error("Invalid path"));
const tool = findTool("pg_jsonb_validate_path");
const result = (await tool!.handler(
{
path: "$.invalid[[[",
},
mockContext,
)) as { valid: boolean; error: string };
expect(result.valid).toBe(false);
expect(result.error).toBeDefined();
});
});
describe("pg_jsonb_merge", () => {
it("should merge two JSONB documents with deep merge", async () => {
// Deep merge now happens entirely in TypeScript, no PostgreSQL call needed
const tool = findTool("pg_jsonb_merge");
const result = (await tool!.handler(
{
base: { a: 1, b: 2 },
overlay: { c: 3 },
},
mockContext,
)) as { merged: Record<string, number>; deep: boolean };
expect(result.merged).toEqual({ a: 1, b: 2, c: 3 });
expect(result.deep).toBe(true);
// Deep merge no longer calls PostgreSQL
});
it("should shallow merge with deep=false", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ result: { a: 1, b: 2, c: 3 } }],
});
const tool = findTool("pg_jsonb_merge");
const result = (await tool!.handler(
{
base: { a: 1, b: 2 },
overlay: { c: 3 },
deep: false,
},
mockContext,
)) as { merged: Record<string, number>; deep: boolean };
expect(result.merged).toEqual({ a: 1, b: 2, c: 3 });
expect(result.deep).toBe(false);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("||"),
expect.anything(),
);
});
});
describe("pg_jsonb_normalize", () => {
it("should normalize JSONB to key-value pairs", async () => {
// First call: idColumn detection
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] }); // No 'id' column
// Second call: actual query
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ key: "name", value: "John" },
{ key: "age", value: "30" },
],
});
const tool = findTool("pg_jsonb_normalize");
const result = (await tool!.handler(
{
table: "users",
column: "data",
mode: "keys",
},
mockContext,
)) as { rows: unknown[]; count: number };
expect(result.count).toBe(2);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_each_text"),
);
});
it("should expand arrays to rows", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ element: "a" }, { element: "b" }],
});
const tool = findTool("pg_jsonb_normalize");
await tool!.handler(
{
table: "data",
column: "items",
mode: "array",
},
mockContext,
);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("jsonb_array_elements"),
);
});
});
describe("pg_jsonb_diff", () => {
it("should compare two JSONB documents", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ key: "name", status: "modified", value1: "John", value2: "Jane" },
{
key: "email",
status: "added",
value1: null,
value2: "jane@example.com",
},
],
});
const tool = findTool("pg_jsonb_diff");
const result = (await tool!.handler(
{
doc1: { name: "John" },
doc2: { name: "Jane", email: "jane@example.com" },
},
mockContext,
)) as { differences: unknown[]; hasDifferences: boolean };
expect(result.hasDifferences).toBe(true);
expect(result.differences).toHaveLength(2);
});
});
describe("pg_jsonb_index_suggest", () => {
it("should suggest indexes based on key distribution", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({
rows: [
{ key: "status", frequency: 800, value_type: "string" },
{ key: "created_at", frequency: 1000, value_type: "string" },
],
})
.mockResolvedValueOnce({ rows: [] });
const tool = findTool("pg_jsonb_index_suggest");
const result = (await tool!.handler(
{
table: "events",
column: "data",
},
mockContext,
)) as { recommendations: string[] };
expect(result.recommendations.length).toBeGreaterThan(0);
expect(result.recommendations[0]).toContain("GIN");
});
});
describe("pg_jsonb_security_scan", () => {
it("should detect sensitive keys", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ count: 50 }] }) // Count query
.mockResolvedValueOnce({
rows: [{ key: "password", count: 5 }],
})
.mockResolvedValueOnce({ rows: [] })
.mockResolvedValueOnce({ rows: [] }); // XSS scan
const tool = findTool("pg_jsonb_security_scan");
const result = (await tool!.handler(
{
table: "users",
column: "data",
},
mockContext,
)) as {
issues: Array<{ type: string }>;
riskLevel: string;
scannedRows: number;
};
expect(result.issues).toHaveLength(1);
expect(result.issues[0].type).toBe("sensitive_key");
expect(result.riskLevel).toBe("medium");
expect(result.scannedRows).toBe(50);
});
it("should detect SQL injection patterns", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ count: 100 }] }) // Count query
.mockResolvedValueOnce({ rows: [] })
.mockResolvedValueOnce({
rows: [{ key: "comment", count: 2 }],
})
.mockResolvedValueOnce({ rows: [] }); // XSS scan
const tool = findTool("pg_jsonb_security_scan");
const result = (await tool!.handler(
{
table: "posts",
column: "data",
},
mockContext,
)) as { issues: Array<{ type: string }> };
expect(
result.issues.some((i) => i.type === "sql_injection_pattern"),
).toBe(true);
});
it("should report low risk when no issues", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({ rows: [{ count: 100 }] }) // Count query
.mockResolvedValueOnce({ rows: [] })
.mockResolvedValueOnce({ rows: [] })
.mockResolvedValueOnce({ rows: [] }); // XSS scan
const tool = findTool("pg_jsonb_security_scan");
const result = (await tool!.handler(
{
table: "clean_data",
column: "data",
},
mockContext,
)) as { riskLevel: string };
expect(result.riskLevel).toBe("low");
});
});
describe("pg_jsonb_stats", () => {
it("should return JSONB statistics", async () => {
mockAdapter.executeQuery
.mockResolvedValueOnce({
rows: [
{
total_rows: 1000,
non_null_count: 950,
avg_size_bytes: 256,
max_size_bytes: 2048,
},
],
})
.mockResolvedValueOnce({
rows: [
{ key: "status", frequency: 950 },
{ key: "type", frequency: 900 },
],
})
.mockResolvedValueOnce({
rows: [{ type: "object", count: 950 }],
});
const tool = findTool("pg_jsonb_stats");
const result = (await tool!.handler(
{
table: "events",
column: "data",
},
mockContext,
)) as {
basics: { total_rows: number };
topKeys: unknown[];
typeDistribution: unknown[];
};
expect(result.basics.total_rows).toBe(1000);
expect(result.topKeys).toHaveLength(2);
expect(result.typeDistribution).toHaveLength(1);
});
});
it("should export all 19 JSONB tools", () => {
expect(tools).toHaveLength(19);
const toolNames = tools.map((t) => t.name);
// Basic tools
expect(toolNames).toContain("pg_jsonb_extract");
expect(toolNames).toContain("pg_jsonb_set");
expect(toolNames).toContain("pg_jsonb_insert");
expect(toolNames).toContain("pg_jsonb_delete");
expect(toolNames).toContain("pg_jsonb_contains");
expect(toolNames).toContain("pg_jsonb_path_query");
expect(toolNames).toContain("pg_jsonb_agg");
expect(toolNames).toContain("pg_jsonb_object");
expect(toolNames).toContain("pg_jsonb_array");
expect(toolNames).toContain("pg_jsonb_keys");
expect(toolNames).toContain("pg_jsonb_strip_nulls");
expect(toolNames).toContain("pg_jsonb_typeof");
// Advanced tools
expect(toolNames).toContain("pg_jsonb_validate_path");
expect(toolNames).toContain("pg_jsonb_merge");
expect(toolNames).toContain("pg_jsonb_normalize");
expect(toolNames).toContain("pg_jsonb_diff");
expect(toolNames).toContain("pg_jsonb_index_suggest");
expect(toolNames).toContain("pg_jsonb_security_scan");
expect(toolNames).toContain("pg_jsonb_stats");
});
});
