query-examples.ts•4.07 kB
/**
* Query Examples: Demonstrating JSON vs JSONB operators
*
* This script shows:
* 1. Basic queries that work with both JSON and JSONB
* 2. JSONB-specific operators that don't work with JSON
* 3. Error cases when trying to use JSONB operators with JSON
*/
import dbService from "./services/db-service.js";
async function main(): Promise<void> {
try {
// Initialize the database service
await dbService.initialize();
// Drop existing table if it exists
await dbService.query("DROP TABLE IF EXISTS json_operators;");
// Create a table with both JSON and JSONB columns
await dbService.query(`
CREATE TABLE json_operators (
id SERIAL PRIMARY KEY,
data_json JSON,
data_jsonb JSONB
);
`);
console.log("Created table with JSON and JSONB columns");
// Sample data
const sampleData = {
name: "John Doe",
age: 30,
preferences: {
theme: "dark",
notifications: true,
},
};
// Insert the same data into both columns
await dbService.query(
`INSERT INTO json_operators (data_json, data_jsonb)
VALUES ($1::json, $1::jsonb)`,
[JSON.stringify(sampleData)]
);
console.log("Inserted sample data into both columns");
console.log(
"\n----- ERROR DEMONSTRATION: JSONB-SPECIFIC OPERATORS WITH JSON -----"
);
console.log(
"Attempting to use JSONB operators on both JSON and JSONB columns...\n"
);
// 1. Containment operator (@>)
console.log("1. Testing containment operator (@>):");
try {
await dbService.query(`
SELECT * FROM json_operators
WHERE data_json @> '{"preferences": {"theme": "dark"}}'::jsonb;
`);
} catch (error: any) {
console.log("JSON column error:", error.message);
}
const jsonbResult = await dbService.query(`
SELECT * FROM json_operators
WHERE data_jsonb @> '{"preferences": {"theme": "dark"}}'::jsonb;
`);
console.log(
"JSONB column success:",
jsonbResult.rows.length > 0 ? "Match found" : "No match"
);
// 2. Existence operator (?)
console.log("\n2. Testing existence operator (?):");
try {
await dbService.query(`
SELECT * FROM json_operators
WHERE data_json ? 'name';
`);
} catch (error: any) {
console.log("JSON column error:", error.message);
}
const jsonbExistsResult = await dbService.query(`
SELECT * FROM json_operators
WHERE data_jsonb ? 'name';
`);
console.log(
"JSONB column success:",
jsonbExistsResult.rows.length > 0 ? "Key exists" : "Key not found"
);
// 3. Path exists operator (?|)
console.log("\n3. Testing path exists operator (?|):");
try {
await dbService.query(`
SELECT * FROM json_operators
WHERE data_json ?| array['name', 'age'];
`);
} catch (error: any) {
console.log("JSON column error:", error.message);
console.log(
" This error is expected because the ?| operator is JSONB-specific and cannot be used with JSON columns."
);
console.log(
" The error demonstrates a key difference between JSON and JSONB in PostgreSQL."
);
}
const jsonbPathResult = await dbService.query(`
SELECT * FROM json_operators
WHERE data_jsonb ?| array['name', 'age'];
`);
console.log(
"JSONB column success:",
jsonbPathResult.rows.length > 0 ? "Paths exist" : "Paths not found"
);
console.log("\nSummary of JSONB vs JSON Operator Support:");
console.log("✓ JSONB supports special operators like @>, ?, ?|, etc.");
console.log("✗ JSON does not support these operators");
console.log(
"✗ Attempting to use JSONB operators with JSON will raise an error"
);
console.log(
"\nRecommendation: Use JSONB when you need to use these powerful operators for querying JSON data."
);
} catch (error) {
console.error("Error:", error);
} finally {
await dbService.close();
}
}
main();