PostgreSQL JSONB Interview Questions

Master PostgreSQL JSONB with interview-focused questions covering JSON vs JSONB, operators, querying, indexing, GIN indexes, containment, updates, nested JSON, performance optimization, and enterprise production best practices.

Introduction

Modern enterprise applications rarely store only relational data.

Applications today manage

User Preferences
Product Attributes
Event Payloads
Configuration Settings
Audit Logs
IoT Data
API Responses

These datasets are often semi-structured, making traditional relational tables less suitable.

PostgreSQL provides JSONB (Binary JSON), allowing developers to combine relational and document-oriented storage within the same database.

JSONB offers

Flexible Schema
Fast Queries
Index Support
ACID Transactions
High Performance

It is widely used in Spring Boot microservices, SaaS platforms, and cloud-native applications.

JSONB Architecture

flowchart LR

Application --> JsonbColumnGinIndex["JSONB Column --> GIN Index --> PostgreSQL --> Disk"]

1. What is JSONB?

Answer

JSONB (JavaScript Object Notation Binary) is PostgreSQL's binary representation of JSON.

Unlike JSON,

JSONB is stored in an optimized binary format.

Benefits

Faster Queries
Efficient Storage
Index Support
Better Performance

2. Why is JSONB used?

JSONB is useful when

Structure changes frequently
Attributes vary between records
Nested objects are required
Document-style storage is needed

Examples

Product Specifications
User Settings
Shopping Cart
Event Metadata

3. What is the difference between JSON and JSONB?

JSON	JSONB
Text Format	Binary Format
Preserves Formatting	Removes Formatting
Slower Queries	Faster Queries
Limited Indexing	Supports GIN Index
Faster Inserts	Faster Reads

JSON vs JSONB

flowchart LR

JSON --> TextStorage

JSONB --> BinaryStorage

BinaryStorage --> GINIndex

4. How do you create a JSONB column?

Example

CREATE TABLE users (

id SERIAL PRIMARY KEY,

profile JSONB

);

5. How do you insert JSONB data?

Example

INSERT INTO users(profile)

VALUES (

'{

"name":"Venu",

"city":"San Antonio",

"skills":["Java","Spring"]

}'

);

6. How do you retrieve JSONB data?

SELECT profile

FROM users;

7. How do you access a JSON field?

Operator

->

Example

SELECT

profile->'city'

FROM users;

Returns JSON.

8. How do you retrieve text values?

Operator

->>

Example

SELECT

profile->>'city'

FROM users;

Returns plain text.

-> vs ->>

Operator	Returns
->	JSON
->>	Text

9. How do you access nested JSON?

Example

{

"address":{

"city":"Austin"

}

}

Query

SELECT

profile->'address'->>'city'

FROM users;

Nested JSON

flowchart TD

JSONB --> Address

Address --> City

10. How do you check if a key exists?

Operator

Example

SELECT *

FROM users

WHERE profile ? 'city';

11. How do you check multiple keys?

Operator

?&

Example

WHERE profile ?&

ARRAY['city','skills']

12. How do you check any key?

Operator

?|

Example

WHERE profile ?|

ARRAY['phone','email']

13. What is the containment operator?

Operator

Actually in PostgreSQL the containment operator is

@>

Example

SELECT *

FROM users

WHERE profile @>

'{"city":"Austin"}';

JSONB Containment

flowchart LR

JSONDocument --> ContainmentCheck --> MatchingRows

14. How do you update a JSONB value?

Use

jsonb_set()

Example

UPDATE users

SET profile =

jsonb_set(

profile,

'{city}',

'"Dallas"'

);

15. How do you remove a key?

Operator

Example

UPDATE users

SET profile =

profile - 'phone';

16. What is GIN Index?

GIN

Generalized Inverted Index

is optimized for

JSONB
Arrays
Full Text Search

Provides extremely fast lookups.

GIN Index

CREATE INDEX idx_profile

ON users

USING GIN(profile);

GIN Architecture

flowchart LR

JSONB --> GINIndex --> FastSearch

17. Why is GIN faster?

Instead of scanning

every JSON document,

GIN indexes

individual keys and values.

18. What is GiST Index?

GiST

Generalized Search Tree

supports

Spatial Data
Geometric Types
Full Text Search

GIN is generally preferred for JSONB.

GIN vs GiST

GIN	GiST
JSONB	Spatial Data
Arrays	Geometry
Fast Lookup	Flexible Structure

19. Can JSONB store arrays?

Yes.

Example

{

"skills":[

"Java",

"Spring",

"Docker"

]

}

20. Can JSONB store nested objects?

Yes.

Unlimited nesting is supported within practical limits.

Example

{

"company":{

"address":{

"city":"Austin"

}

}

}

21. When should JSONB be used?

Good use cases

Flexible Attributes
Event Data
Configuration
Product Metadata
API Payloads
Audit Logs

22. When should JSONB NOT be used?

Avoid JSONB for

Frequently joined relational data
Primary business relationships
Strongly normalized entities
Heavy transactional relationships

Use relational tables instead.

Relational vs JSONB

Relational	JSONB
Fixed Schema	Flexible Schema
Joins	Nested Documents
Foreign Keys	Embedded Data

23. Banking Example

Store customer preferences

{

"language":"English",

"notifications":true

}

inside JSONB.

24. E-Commerce Example

Store product specifications

{

"RAM":"16GB",

"CPU":"Intel i7",

"SSD":"1TB"

}

without altering table structure.

25. SaaS Example

Tenant configuration

stored as JSONB.

Different customers

have different configuration fields.

26. IoT Example

Sensor payload

{

"temperature":27,

"humidity":68,

"battery":91

}

27. Common JSONB Mistakes

Using JSON instead of JSONB
Forgetting GIN indexes
Storing all application data in JSONB
Large unindexed documents
Deeply nested structures
Ignoring relational design

28. Performance Tips

Prefer JSONB over JSON.
Create GIN indexes.
Index frequently queried expressions.
Keep documents reasonably small.
Use relational tables for core business entities.
Analyze execution plans.

JSONB Query Workflow

flowchart LR

Application --> JsonbQueryGinIndex["JSONB Query --> GIN Index --> PostgreSQL --> Result"]

Enterprise Best Practices

Prefer JSONB for production workloads.
Create GIN indexes for searchable JSONB columns.
Store relational data in normalized tables.
Use JSONB for flexible attributes only.
Avoid excessive nesting.
Monitor JSONB query performance.
Combine relational columns with JSONB where appropriate.
Validate JSON structure in the application layer.
Benchmark large JSONB queries.
Review execution plans regularly.

Quick Revision

Topic	Key Point
JSONB	Binary JSON Storage
JSON	Text JSON
->	Returns JSON
->>	Returns Text
@>	Containment
?	Key Exists
?&	All Keys Exist
?
jsonb_set()	Update JSON
-	Remove Key
GIN Index	Fast JSONB Search
GiST	Spatial Index

Interview Tips

Interviewers frequently ask

JSON vs JSONB.
Why is JSONB faster?
Explain GIN Index.
What does -> return?
What does ->> return?
How do you update JSONB?
How do you remove a JSON key?
When should JSONB be used?
When should relational tables be preferred?
Explain a real production use case.

A strong interview explanation is:

"JSONB stores JSON data in an optimized binary format, allowing PostgreSQL to efficiently search and index document data. Combined with GIN indexes, JSONB provides excellent performance for semi-structured data while retaining full ACID transaction support. In enterprise applications, JSONB is commonly used for flexible attributes, configuration, audit data, and event payloads, while core business entities remain normalized in relational tables."

Summary

PostgreSQL JSONB combines the flexibility of document databases with the reliability and transactional guarantees of a relational database. Features such as binary storage, GIN indexing, containment operators, nested document support, and efficient updates make JSONB an excellent choice for handling semi-structured enterprise data.

Understanding JSONB operators, indexing strategies, performance optimization, and when to choose JSONB versus normalized relational tables is essential for Backend Developers, Database Engineers, Spring Boot Developers, and Solution Architects building modern cloud-native applications.