Using PostgreSQL JSONB for Flexible Schemas

Introduction

PostgreSQL’s JSONB enables storing semi-structured data with indexing and querying support, blending NoSQL flexibility with SQL power.

Prerequisites

• PostgreSQL >=9.4
• psql access

Step 1: Create Table with JSONB Column

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

Step 2: Insert JSONB Data

INSERT INTO products (name, attributes) VALUES
('T-Shirt', '{"size":"M","color":"blue","tags":["summer","casual"]}'),
('Coffee Mug', '{"capacity":300,"material":"ceramic"}');

Step 3: Query JSONB Fields

Filter by attribute:

SELECT * FROM products
WHERE attributes->>'color' = 'blue';

Extract nested data:

SELECT attributes->'tags' AS tags FROM products;

Step 4: Update JSONB Fields

Use jsonb_set:

UPDATE products
SET attributes = jsonb_set(attributes, '{color}', '"red"')
WHERE id = 1;

Step 5: Indexing JSONB Data

Create GIN index for efficient queries:

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

For specific key:

CREATE INDEX idx_products_color ON products ((attributes->>'color'));

Summary

JSONB in PostgreSQL offers dynamic schemas with full indexing and rich querying capabilities, enabling flexible data storage with SQL reliability.