Bits of .NET

📊 NoSQL in SQL, Best of Both Worlds

Store flexible data as JSON. Query inside JSON. Index specific fields. PostgreSQL JSON functions give you relational + document database.

📝 Store and Query JSON

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    metadata JSONB
);

INSERT INTO products (name, metadata) VALUES 
('Laptop', '{"brand": "Apple", "ram": 16, "colors": ["space-gray", "silver"]}'),
('Phone', '{"brand": "Samsung", "ram": 8, "colors": ["black", "white"]}');

-- Query JSON fields
SELECT name, metadata->>'brand' as brand
FROM products
WHERE metadata->>'brand' = 'Apple';

-- Nested access
SELECT name, metadata->'colors'->>0 as first_color
FROM products;

-- Search in JSON array
SELECT name
FROM products
WHERE metadata->'colors' ? 'space-gray';

🎯 JSON Operators

-- -> : Get as JSON (with quotes)
-- ->> : Get as text (without quotes)
-- #> : Get nested by path
-- #>> : Get nested as text

-- Check if key exists
SELECT * FROM products WHERE metadata ? 'brand';

-- Check if any in array
SELECT * FROM products WHERE metadata->'colors' ? 'space-gray';

-- Update JSON (atomic)
UPDATE products
SET metadata = metadata || '{"warranty": "2 years"}'
WHERE id = 1;

-- Remove key
UPDATE products
SET metadata = metadata - 'warranty'
WHERE id = 1;

💡 Indexing JSON

• GIN index on entire JSON: CREATE INDEX idx_gin ON products USING GIN (metadata);
• Index specific key: CREATE INDEX idx_brand ON products ((metadata->>'brand'));
• B-tree on extracted path for equality searches