SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';

SELECT * FROM products WHERE attributes ? 'wireless';

SELECT data->>'name' AS name, data->'address'->>'city' AS city FROM users;

SELECT * FROM events WHERE data #> '{user,settings,notifications}' = 'true';

-- Index trường email trong JSONB
CREATE INDEX idx_user_email ON users ((data->>'email'));

-- Index cho truy vấn kiểm tra tồn tại khóa
CREATE INDEX idx_attributes ON products USING GIN (attributes);

-- Index cho toán tử containment (@>)
CREATE INDEX idx_preferences ON users USING GIN (preferences);

-- Tạo chỉ mục full-text search trên trường content trong JSONB
CREATE INDEX idx_content_search ON articles USING GIN (to_tsvector('english', data->>'content'));

-- Truy vấn với từ khóa tìm kiếm
SELECT * FROM articles WHERE to_tsvector('english', data->>'content') @@ plainto_tsquery('postgres jsonb amazing');

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    preferences JSONB DEFAULT '{}',
    metadata JSONB DEFAULT '{}'
);

INSERT INTO users (email, preferences) VALUES (
    '[email protected]',
    '{
        "theme": "dark",
        "notifications": {
            "email": true,
            "push": false,
            "frequency": "daily"
        },
        "features": {
            "beta": true,
            "advancedAnalytics": false
        }
    }'
);

SELECT email FROM users WHERE preferences @> '{"theme": "dark", "notifications": {"email": true}}';

UPDATE users SET preferences = jsonb_set(preferences, '{notifications,push}', 'true')
WHERE email = '[email protected]';

CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type TEXT NOT NULL,
    user_id UUID,
    occurred_at TIMESTAMPTZ DEFAULT NOW(),
    data JSONB NOT NULL
);

CREATE INDEX idx_events_type_data ON events (event_type) WHERE event_type IN ('purchase', 'signup', 'feedback');
CREATE INDEX idx_events_data ON events USING GIN (data);

INSERT INTO events (event_type, user_id, data) VALUES
('signup', 'user-123', '{"source": "google", "campaign": "summer-2024", "referrer": "blog-post"}'),
('purchase', 'user-123', '{"items": [ {"sku": "PROD-1", "quantity": 2, "price": 49.99}, {"sku": "PROD-2", "quantity": 1, "price": 19.99} ], "discount": "SUMMER20", "total": 99.97}'),
('feedback', 'user-123', '{"type": "feature_request", "title": "Add dark mode", "priority": "high", "tags": ["ui", "accessibility"]}');

SELECT SUM((data->>'total')::NUMERIC) AS total_revenue
FROM events
WHERE event_type = 'purchase'
AND occurred_at >= NOW() - INTERVAL '30 days';

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    attributes JSONB DEFAULT '{}'
);

INSERT INTO products (name, price, attributes) VALUES
('iPhone 15', 999.00, '{"brand": "Apple", "storage": "256GB", "color": "Blue", "5g": true, "screen": { "size": "6.1 inches", "type": "OLED", "resolution": "2532x1170" }}'),
('Nike Air Max', 120.00, '{"brand": "Nike", "size": "10", "color": "Black/White", "material": "Mesh", "style": "Running"}'),
('The Pragmatic Programmer', 39.99, '{"author": "David Thomas", "isbn": "978-0135957059", "pages": 352, "publisher": "Addison-Wesley", "edition": "2nd"}');

SELECT name, price FROM products WHERE attributes @> '{"5g": true}';

SELECT * FROM products WHERE attributes->>'brand' = 'Apple';

SELECT name, attributes->'screen'->>'size' AS screen_size FROM products
WHERE (attributes->'screen'->>'size')::FLOAT > 6.0;

CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(id),
    total NUMERIC(10,2) NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    line_items JSONB,
    metadata JSONB
);

import psycopg2
from pymongo import MongoClient
from psycopg2.extras import Json

# Kết nối MongoDB và PostgreSQL
mongo = MongoClient('mongodb://localhost:27017/')
postgres = psycopg2.connect("postgresql://...")

# Di cư dữ liệu
for doc in mongo.mydb.mycollection.find():
    postgres.cursor().execute(
        "INSERT INTO my_table (id, data) VALUES (%s, %s)",
        (str(doc['_id']), Json(doc))
    )