Definition
->and->>–->extracts a JSON sub-element as JSON;->>extracts as text, which is what you use for comparisons and indexing.jsonb_build_object– constructs a JSON object from key/value pairs, useful for building API responses in SQL.jsonb_agg– aggregates multiple rows into a single JSON array.jsonb_set– updates a specific path in a JSONB document and returns the new document.jsonb_array_elements/json_each– expand arrays into rows and objects into key/value rows for joins and aggregation.- Design rule – normalize structured, high-value data (balances, IDs, timestamps) into typed columns; use JSON for sparse, flexible, or evolving attributes read as a whole.
For National Bank of India, structured data like account balance and holder name belongs in proper columns with types, constraints, and indexes. Variable extras – a customer's optional preferences, KYC fields that differ per customer type, or a list of nominees – fit a JSON column. The JSON functions let the bank build, read, and reshape those flexible fields right inside SQL instead of pulling everything into Java.
Understanding the Concepts
What do the accessor operators -> and ->> do?
The -> operator extracts a JSON sub-element and returns it as JSON. The ->> operator extracts it as text, which is what you use for comparisons, WHERE clauses, and indexing.
-- Sample data: customers with JSONB kyc column
SELECT customer_id,
kyc->'address' AS address_json, -- returns JSON object
kyc->>'status' AS status_text -- returns text: 'active'
FROM customers
WHERE kyc->>'status' = 'active'; -- text comparison works
customer_id | address_json | status_text
------------+------------------------------------+-------------
C001 | {"city":"Mumbai","pin":"400001"} | active
C002 | {"city":"Delhi","pin":"110001"} | active
How do you build JSON objects with jsonb_build_object?
jsonb_build_object constructs a JSON object from key/value pairs. This is useful for building API responses directly in SQL.
-- Build a JSON response per account
SELECT jsonb_build_object(
'accountId', account_id,
'holderName', holder_name,
'balance', balance
) AS account_json
FROM accounts
WHERE account_id = 'SAV2001';
account_json
--------------------------------------------------
{"accountId":"SAV2001","holderName":"Rajesh","balance":500000.00}
How do you aggregate rows into a JSON array with jsonb_agg?
jsonb_agg rolls many rows into one JSON array. This is perfect for returning a customer's transactions as a nested array in one query.
-- All transactions for an account as a JSON array
SELECT account_id,
jsonb_agg(
jsonb_build_object(
'txnId', txn_id,
'amount', amount,
'date', txn_date
) ORDER BY txn_date DESC
) AS transactions
FROM transactions
WHERE account_id = 'SAV2001'
GROUP BY account_id;
account_id | transactions
-----------+----------------------------------------------------------
SAV2001 | [{"txnId":"T003","amount":-10000,"date":"2024-03-15"},
| {"txnId":"T002","amount":50000,"date":"2024-03-10"}]
How do you update a JSON path with jsonb_set?
jsonb_set returns a new document with a specific path changed. It does not modify in place – you must assign the result back.
-- Update KYC status from 'pending' to 'active'
UPDATE customers
SET kyc = jsonb_set(kyc, '{status}', '"active"')
WHERE customer_id = 'C001';
-- Add a new field to the KYC document
UPDATE customers
SET kyc = jsonb_set(kyc, '{verified_at}', to_jsonb(NOW()))
WHERE customer_id = 'C001';
How do you expand JSON arrays and objects into rows?
jsonb_array_elements turns a JSON array into rows. json_each turns a JSON object into key/value rows.
-- Expand nominees array into rows
SELECT customer_id,
nominee->>'name' AS nominee_name,
nominee->>'relation' AS relation
FROM customers,
jsonb_array_elements(kyc->'nominees') AS nominee
WHERE customer_id = 'C001';
customer_id | nominee_name | relation
------------+--------------+----------
C001 | Priya | spouse
C001 | Anita | mother
-- Expand KYC object into key/value rows
SELECT customer_id, key, value
FROM customers,
json_each(kyc::json) AS t(key, value)
WHERE customer_id = 'C001';
When should you use JSON vs normalized columns?
Normalize structured, high-value data that you query, constrain, join, and index. Use JSON for schema-less or sparse data that varies per row, changes frequently, or is read as a whole blob.
| Criteria | Normalized columns | JSON column |
|----------|-------------------|-------------|
| Query in WHERE | Frequently | Rarely |
| JOIN target | Yes | No |
| Constraints (NOT NULL, CHECK) | Yes | Not directly |
| Data shape | Fixed, consistent | Variable, sparse |
| Index support | Full b-tree | GIN index on JSONB |
| Schema evolution | ALTER TABLE | Just add a key |
-- Normalized: balance, account_id (queried, constrained, joined)
CREATE TABLE accounts (
account_id VARCHAR(10) PRIMARY KEY,
holder_name VARCHAR(100) NOT NULL,
balance DECIMAL(15,2) NOT NULL CHECK (balance >= 0)
);
-- JSON: KYC extras (sparse, variable per customer type)
ALTER TABLE customers ADD COLUMN kyc JSONB DEFAULT '{}';
How do you index a JSON key for fast filtering?
When you find yourself constantly filtering on a JSON key, create a GIN index or a functional index.
-- GIN index: supports @>, ?, and other JSONB operators
CREATE INDEX idx_customers_kyc ON customers USING GIN (kyc);
-- Functional index: fast equality on a specific key
CREATE INDEX idx_customers_kyc_status ON customers ((kyc->>'status'));
-- Query uses the functional index:
SELECT * FROM customers WHERE kyc->>'status' = 'active';
When should you promote a JSON key to a real column?
The moment you find yourself constantly filtering, joining, or aggregating on a JSON key, promote it to a real column. You can use a generated column to keep it synchronized.
-- Generated column indexed from JSON
ALTER TABLE customers
ADD COLUMN kyc_status VARCHAR(20)
GENERATED ALWAYS AS (kyc->>'status') STORED;
CREATE INDEX idx_customers_kyc_status_col ON customers (kyc_status);
-- Now query uses a regular indexed column:
SELECT * FROM customers WHERE kyc_status = 'active';
How do you use JSON functions in Java?
package com.bank.service;
import java.sql.*;
public class CustomerSummaryService {
private final Connection connection;
public CustomerSummaryService(Connection connection) {
this.connection = connection;
}
public String getCustomerSummaryJson(String customerId) throws SQLException {
String sql = """
SELECT jsonb_build_object(
'customerId', c.customer_id,
'name', c.full_name,
'kycStatus', c.kyc->>'status',
'nominees', c.kyc->'nominees',
'accounts', (
SELECT jsonb_agg(jsonb_build_object(
'accountId', a.account_id,
'balance', a.balance
)) FROM accounts a WHERE a.customer_id = c.customer_id
)
) AS summary
FROM customers c
WHERE c.customer_id = ?
""";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, customerId);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return rs.getString("summary");
}
}
}
return null;
}
}
What are common mistakes?
The most common mistake is stuffing everything into JSON, losing the benefits of typed columns.
-- Wrong: balance in JSON (no constraint, no type safety, no direct index)
UPDATE customers SET kyc = jsonb_set(kyc, '{balance}', '500000');
-- How do you enforce balance >= 0? How do you SUM balances efficiently?
-- Correct: balance in a typed, constrained column
ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);
Another mistake is JSON updates on very large documents. jsonb_set rewrites the whole document, so a 1MB document updated frequently is costly.
What are common interview follow-up questions?
- "What is the difference between JSON and JSONB in PostgreSQL?" JSONB is a binary format that supports indexing, containment operators, and faster reads; JSON stores the exact text and is cheaper to write but slower to query. - "Can you use JSON columns in MySQL?" Yes, MySQL 5.7+ supports a JSON type with `->` and `->>` operators and functional indexes. - "How do you validate JSON structure?" Use CHECK constraints with `jsonb_typeof` or application-level validation.
Visual Overview
JSON Functions Overview:
┌──────────────────────────────────────────────────────────┐
│ Accessor: kyc->'address' → JSON sub-element │
│ kyc->>'status' → text value │
├──────────────────────────────────────────────────────────┤
│ Build: jsonb_build_object('id', id, 'bal', bal) │
│ → {"id":"SAV2001","bal":500000} │
├──────────────────────────────────────────────────────────┤
│ Aggregate: jsonb_agg(...) │
│ → [{"txnId":"T1",...}, {"txnId":"T2",...}] │
├──────────────────────────────────────────────────────────┤
│ Modify: jsonb_set(kyc, '{status}', '"active"') │
│ → new document with status changed │
├──────────────────────────────────────────────────────────┤
│ Expand: jsonb_array_elements(kyc->'nominees') │
│ → one row per nominee │
│ json_each(kyc) │
│ → one row per key/value pair │
└──────────────────────────────────────────────────────────┘
When to use JSON vs Columns:
Columns (normalized): JSON (flexible):
┌──────────────────────┐ ┌──────────────────────┐
│ account_id (PK) │ │ kyc JSONB │
│ holder_name (NOT NULL)│ │ { "status": "active",│
│ balance (CHECK >= 0) │ │ "nominees": [...], │
│ branch_code (FK) │ │ "preferences": {} │
└──────────────────────┘ │ } │
Queried, constrained, └──────────────────────┘
joined, indexed Sparse, variable,
read as whole blob
Key Takeaway
The common JSON functions let you build, read, modify, and unnest documents inside SQL. The single arrow returns a value as JSON and the double arrow returns it as text. jsonb_build_object constructs an object per row; jsonb_agg rolls many rows into one JSON array, which is perfect for returning nested collections in a single query. jsonb_set updates a specific path and returns a new document. jsonb_array_elements and json_each expand arrays and objects into rows so you can aggregate or join them.
The design rule is to normalize the data you query, constrain, join, and index – balances, account IDs, timestamps – into proper typed columns, and reserve JSON for sparse, flexible, or evolving attributes that are usually read as a whole, like optional KYC fields or a nominees array. At National Bank of India, Rajesh keeps account_id, holder_name, and balance as constrained columns with indexes, stores the KYC status and nominees in a JSONB column, and uses jsonb_build_object to merge them into one API response.
The rule of thumb is that the moment you find yourself constantly filtering or joining on a JSON key, promote it to a real column, possibly a generated indexed column. JSON updates rewrite the whole document, and large documents updated frequently get expensive. The key is using JSON where it adds flexibility without sacrificing the queryability and integrity that typed columns provide.
Remember one sentence: normalize what you query, constrain, and join, and put flexible, rarely-filtered attributes in JSON.
---