PostgreSQL: Manipulação de Dados JSON e JSONB no PostgreSQL

SELECT '{"name": "Alice", "age": 30}'::json;
-- {"name": "Alice", "age": 30}

SELECT '{"name": "Alice", "age": 30}'::jsonb;
-- {"age": 30, "name": "Alice"}

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  profile JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO users (username, profile) VALUES
  ('alice', '{"theme": "dark", "language": "en", "notifications": true}'),
  ('bob', '{"timezone": "UTC-5", "currency": "USD", "dashboard_layout": "grid"}');

INSERT 0 1

SELECT profile -> 'theme' AS theme_json,
       profile ->> 'theme' AS theme_text
FROM users
WHERE username = 'alice';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT profile #> '{address,city}' AS city_json,
       profile #>> '{address,city}' AS city_text
FROM users
WHERE profile ? 'address';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT username, profile
FROM users
WHERE profile @> '{"theme": "dark"}';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT username, profile ->> 'timezone' AS tz
FROM users
WHERE profile ? 'timezone';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT username
FROM users
WHERE profile ?| array['timezone', 'currency'];

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  attributes JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO products (name, attributes) VALUES
  ('T-Shirt', '{"colors": ["red", "blue", "green"], "sizes": ["S", "M", "L"]}'),
  ('Laptop', '{"colors": ["silver", "black"], "warranty_years": 2}');

SELECT product_id, name,
       jsonb_array_elements_text(attributes -> 'colors') AS color
FROM products;

INSERT 0 1

SELECT username,
       (jsonb_each(profile)).key AS config_key,
       (jsonb_each(profile)).value AS config_value
FROM users;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Adiciona ou atualiza um campo
UPDATE users
SET profile = jsonb_set(profile, '{language}', '"zh"')
WHERE username = 'alice';

-- Adiciona um campo aninhado
UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"New York"')
WHERE username = 'alice';

-- Remove um campo
UPDATE users
SET profile = profile - 'notifications'
WHERE username = 'alice';

-- Comando SQL executado com sucesso

-- Anexa ao final (o caminho deve apontar para um array existente, insere após o último elemento)
UPDATE products
SET attributes = jsonb_set(
  attributes, '{colors}',
  (attributes -> 'colors') || '"yellow"'
)
WHERE name = 'T-Shirt';

INSERT 0 1

SELECT jsonb_pretty(profile) FROM users WHERE username = 'alice';

{
    "theme": "dark",
    "language": "zh",
    "address": {
        "city": "New York"
    }
}

-- Índice GIN padrão (suporta @>, ?, ?|, ?&)
CREATE INDEX idx_users_profile ON users USING gin (profile);

-- Índice GIN path ops (menor, mais rápido apenas para @>)
CREATE INDEX idx_users_profile_path ON users USING gin (profile jsonb_path_ops);

CREATE TABLE

-- Sem índice: varredura sequencial
EXPLAIN ANALYZE
SELECT * FROM users WHERE profile @> '{"theme": "dark"}';

-- Após criar índice GIN: varredura de índice bitmap
CREATE INDEX idx_users_profile ON users USING gin (profile);
EXPLAIN ANALYZE
SELECT * FROM users WHERE profile @> '{"theme": "dark"}';

CREATE TABLE

-- Para consultas usando o operador ->>
CREATE INDEX idx_users_theme ON users ((profile ->> 'theme'));
SELECT * FROM users WHERE profile ->> 'theme' = 'dark'; -- Usa índice

CREATE TABLE

SELECT jsonb_path_query(profile, '$.theme') AS theme
FROM users
WHERE username = 'alice';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Produtos com garantia > 1 ano
SELECT name,
       jsonb_path_query(attributes, '$.warranty_years') AS warranty
FROM products
WHERE jsonb_path_exists(attributes, '$.warranty_years ? (@ > 1)');

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT name,
       jsonb_path_query(attributes, '$.colors[*]') AS color
FROM products;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE TABLE products_v2 (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,              -- Coluna fixa, indexada
  price NUMERIC(10,2) NOT NULL,        -- Coluna fixa, indexada
  stock INT NOT NULL DEFAULT 0,         -- Coluna fixa, indexada
  attributes JSONB NOT NULL DEFAULT '{}', -- Atributos dinâmicos
  metadata JSONB DEFAULT '{}'          -- Metadados raramente consultados
);

CREATE INDEX idx_products_category ON products_v2 (category);
CREATE INDEX idx_products_attrs ON products_v2 USING gin (attributes jsonb_path_ops);

CREATE TABLE

ALTER TABLE products_v2
ADD CONSTRAINT chk_attributes_schema
CHECK (
  jsonb_typeof(attributes -> 'colors') = 'array'
  AND attributes ? 'colors'
);

-- Comando SQL executado com sucesso

-- Encontra pedidos onde o produto tem um atributo específico
SELECT o.order_id, o.customer_id, p.name
FROM orders o
JOIN products_v2 p ON o.product_id = p.product_id
WHERE p.attributes @> '{"warranty_years": 2}';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Passo 1: Criar tabelas principais com JSONB
CREATE TABLE saas_users (
  user_id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  plan TEXT NOT NULL DEFAULT 'free',
  config JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE saas_products (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  specs JSONB NOT NULL DEFAULT '{}',
  tags JSONB NOT NULL DEFAULT '[]'
);

-- Passo 2: Inserir dados de exemplo
INSERT INTO saas_users (email, name, plan, config) VALUES
  ('alice@corp.com', 'Alice', 'pro',
   '{"theme":"dark","language":"en","notifications":{"email":true,"sms":false},"sidebar":["dashboard","reports"]}'),
  ('bob@corp.com', 'Bob', 'enterprise',
   '{"theme":"light","language":"zh","notifications":{"email":true,"sms":true},"sidebar":["dashboard","admin","billing"]}');

INSERT INTO saas_products (name, category, price, specs, tags) VALUES
  ('Pro Widget', 'widget', 49.99,
   '{"weight_kg":0.5,"colors":["red","blue"],"warranty_years":3}',
   '["popular","new"]'),
  ('Mega Gadget', 'gadget', 199.99,
   '{"weight_kg":2.0,"colors":["silver","black"],"voltage":"220V"}',
   '["premium","bestseller"]');

-- Passo 3: Criar índices
CREATE INDEX idx_saas_users_config ON saas_users USING gin (config);
CREATE INDEX idx_saas_products_specs ON saas_products USING gin (specs jsonb_path_ops);
CREATE INDEX idx_saas_products_tags ON saas_products USING gin (tags);
CREATE INDEX idx_saas_products_category ON saas_products (category);

-- Passo 4: Exemplos de consulta
-- Encontra usuários com notificações por email ativadas
SELECT name, config ->> 'theme' AS theme
FROM saas_users
WHERE config @> '{"notifications":{"email":true}}';

-- Encontra produtos disponíveis em vermelho
SELECT name, price
FROM saas_products
WHERE specs -> 'colors' @> '["red"]';

-- Encontra produtos com tags específicas
SELECT name
FROM saas_products
WHERE tags @> '["premium"]';

-- Atualiza configuração de usuário (adiciona novo campo)
UPDATE saas_users
SET config = jsonb_set(config, '{timezone}', '"America/New_York"')
WHERE email = 'alice@corp.com';

-- Remove um campo de configuração
UPDATE saas_users
SET config = config - 'language'
WHERE email = 'bob@corp.com';

-- Expande tags de produtos para análise
SELECT name, jsonb_array_elements_text(tags) AS tag
FROM saas_products;

-- Formata a configuração do usuário de forma legível
SELECT name, jsonb_pretty(config) FROM saas_users WHERE plan = 'pro';