PostgreSQL’s JSONB data type gives you the flexibility of a document store while keeping all the relational superpowers you already know. But the truth is simple: most developers struggle with JSONB purely because they forget the syntax. The arrows, operators, casting — it’s easy to mix them up when you haven’t used them in a while.

This guide is the refresher I wish someone handed me years ago. We’ll walk through creating JSONB columns, inserting data, reading values, filtering, updating, and indexing — using examples based on the Pagila Sample Database.

What Exactly Is JSONB?

PostgreSQL has two JSON types: json and jsonb. The “b” stands for binary, which means PostgreSQL stores it in an optimized, index-friendly format.

• json = stored as text, no indexing
• jsonb = binary, deduplicated, sortable, indexable

If you need performance or indexing, always choose jsonb.

Adding a JSONB Column

Let’s extend Pagila’s customer table with a JSONB column.

ALTER TABLE customer
ADD COLUMN metadata JSONB;
    

Insert some JSON data:

UPDATE customer
SET metadata = jsonb_build_object(
  'preferences', jsonb_build_object(
      'email', (customer_id = 1),          -- only customer_id = 1 gets true
      'sms', (customer_id % 3 = 0)         -- true for 3, 6, 9, ...
  ),
  'tags', CASE
            WHEN customer_id % 5 = 0 THEN '["vip", "priority"]'::jsonb
            WHEN customer_id % 2 = 0 THEN '["loyal"]'::jsonb
            ELSE '["new"]'::jsonb
          END
);
    

How to Retrieve Values from JSONB

This is where most people get confused. PostgreSQL gives you two arrow operators:

• -> returns JSON (object or array)
• ->> returns text

Get the whole object:

SELECT metadata->'preferences'
FROM customer;
    

Extract a boolean or string as text:

SELECT metadata->'preferences'->>'email' AS email_opt_in
FROM customer;
    

Extract an array element:

SELECT metadata->'tags'->>0 AS first_tag
FROM customer;
    

Filtering Rows Based on JSONB

JSONB filtering is where the syntax surprises most beginners. You have two main ways to filter: containment and value extraction.

1. Containment: find rows containing JSON structure

SELECT customer_id
FROM customer
WHERE metadata @> '{"preferences": {"email": true}}';
    

Containment checks whether the right-hand JSON is a subset of the left. It’s incredibly powerful — and incredibly misunderstood — but perfect when you want to match entire JSON structures.

2. Extract and compare a specific value

SELECT customer_id
FROM customer
WHERE metadata->'preferences'->>'sms' = 'false';
    

Use this approach when you care about a single field, not a whole structure.

Updating JSONB Values

JSONB isn’t like a regular column — you don’t update individual keys directly. You use jsonb_set, which returns a modified copy.

Update a single key:

UPDATE customer
SET metadata = jsonb_set(
      metadata,
      '{preferences,email}',
      'false'::jsonb
    )
WHERE customer_id = 1;
    

Add a new key:

UPDATE customer
SET metadata = jsonb_set(
      metadata,
      '{profile}',
      '{"created_at": "2025-01-01"}',
      true
    );
    

The final true tells PostgreSQL to create missing keys as needed.

Working with JSONB Arrays

Append to an array:

UPDATE customer
SET metadata = jsonb_set(
      metadata,
      '{tags}',
      (metadata->'tags') || '["new"]'::jsonb
    );
    

Check if an array contains a value:

SELECT customer_id, metadata
FROM customer
WHERE metadata->'tags' ? 'vip';
    

The ? operator checks whether the array contains the given string.

Indexing JSONB (Beginner Edition)

JSONB without indexing is fun for learning… and painful in production. Here are the two simple indexes everyone should start with.

1. GIN index for containment queries

CREATE INDEX idx_customer_metadata_gin
ON customer USING GIN (metadata);
    

2. BTREE expression index for single-key lookups

CREATE INDEX idx_customer_sms_pref
ON customer ((metadata->'preferences'->>'sms'));
    

That’s the beginner-friendly version. Start here — then go read my Advanced JSONB article when you want to get serious!

When to Use JSONB (and When Not To)

JSONB is great for:

• Optional or sparse data
• Storing API payloads
• Per-user configuration
• Fields that change shape over time

JSONB is not great for:

• Highly relational data
• Values you frequently filter on
• Columns that need strict constraints
• Data that must be sorted efficiently

Conclusion

JSONB looks intimidating until you’ve used it a few times. Once the operators click, you realize just how powerful PostgreSQL becomes when you mix relational structure with flexible documents.

Use this guide as your cheat sheet whenever you forget the syntax. And when you’re ready to push JSONB to its limits — indexing, performance tuning, query patterns — check out my Advanced JSONB article!