Definition
- **json** – PostgreSQL type that stores the raw JSON text, preserving whitespace, key order, and duplicate keys; re-parsed on each access. - **jsonb** – PostgreSQL binary JSON type; deduplicated keys, unordered, supports GIN indexes, containment (`@>`), and key existence (`?`). - **MySQL JSON** – a single binary JSON type queried with `JSON_EXTRACT(col, '$.key')` or the `->` / `->>` arrow operators. - **`->`** – returns a JSON value (quoted string, nested object). - **`->>`** – returns unquoted text, suitable for comparison and indexing. - **GIN index** – Generalized Inverted Index; the index type that makes jsonb containment queries fast.
National Bank of India stores flexible KYC details that differ per customer: Rajesh may have a PAN and employer, Priya may have GST and property details. Instead of hundreds of mostly-empty columns, the bank keeps these in a JSON column. jsonb is like filing those details in a sorted, indexed cabinet so you can quickly find "all customers with status active," while json is like keeping the original paper form exactly as submitted.
Understanding the Concepts
What is the json type in PostgreSQL?
The json type stores the raw JSON text exactly as inserted. Whitespace, key order, and duplicate keys are preserved. Every access re-parses the string, which makes it slow for queries.
-- PostgreSQL: json preserves exact text
CREATE TABLE kyc_raw (
customer_id VARCHAR(20) PRIMARY KEY,
details json NOT NULL
);
INSERT INTO kyc_raw VALUES ('C001',
'{"name": "Rajesh Kumar", "status": "active", "pan": "ABCDE1234F"}');
-- Whitespace and key order are preserved exactly
SELECT details FROM kyc_raw WHERE customer_id = 'C001';
-- Returns: {"name": "Rajesh Kumar", "status": "active", "pan": "ABCDE1234F"}
What is the jsonb type in PostgreSQL?
The jsonb type parses the JSON once into a binary tree. Keys are deduplicated and unordered. Access is fast, and it supports GIN indexes, containment (@>), and key existence (?).
-- PostgreSQL: jsonb for queryable JSON
CREATE TABLE kyc (
customer_id VARCHAR(20) PRIMARY KEY,
details jsonb NOT NULL
);
INSERT INTO kyc VALUES ('C001',
'{"name": "Rajesh Kumar", "status": "active", "pan": "ABCDE1234F",
"address": {"city": "Delhi", "pin": "110001"}}');
INSERT INTO kyc VALUES ('C002',
'{"name": "Priya Sharma", "status": "active", "gst": "07AABCU9603R1ZM",
"address": {"city": "Mumbai", "pin": "400001"}}');
How do the -> and ->> operators work?
The -> operator returns a JSON value. The ->> operator returns unquoted text. For nested paths, chain the operators or use #> / #>>.
-- -> returns JSON value (quoted)
SELECT details->'name' FROM kyc WHERE customer_id = 'C001';
-- Returns: "Rajesh Kumar" (with quotes, JSON type)
-- ->> returns text (unquoted)
SELECT details->>'name' FROM kyc WHERE customer_id = 'C001';
-- Returns: Rajesh Kumar (plain text)
-- Nested access: drill into address
SELECT details->'address'->>'city' FROM kyc WHERE customer_id = 'C001';
-- Returns: Delhi
-- Path operator for deep nesting
SELECT details#>>'{address,city}' FROM kyc WHERE customer_id = 'C001';
-- Returns: Delhi
How does MySQL handle JSON?
MySQL has a single binary JSON type. You query it with JSON_EXTRACT or the arrow operators. The -> returns JSON, and ->> returns unquoted text.
-- MySQL: JSON column
CREATE TABLE kyc (
customer_id VARCHAR(20) PRIMARY KEY,
details JSON NOT NULL
);
INSERT INTO kyc VALUES ('C001',
'{"name": "Rajesh Kumar", "status": "active", "pan": "ABCDE1234F"}');
-- JSON_EXTRACT
SELECT JSON_EXTRACT(details, '$.name') FROM kyc;
-- Returns: "Rajesh Kumar" (quoted)
-- Arrow operators (MySQL 5.7+)
SELECT details->'$.name' FROM kyc; -- JSON value (quoted)
SELECT details->>'$.name' FROM kyc; -- text (unquoted)
-- Filter with JSON
SELECT * FROM kyc
WHERE details->>'$.status' = 'active';
Why is jsonb preferred for queries?
jsonb supports GIN indexes and containment operators. A query like "find all active customers" can use a GIN index instead of scanning every row.
-- Create a GIN index on the jsonb column
CREATE INDEX idx_kyc_details ON kyc USING GIN (details);
-- Containment query: uses the GIN index
SELECT customer_id, details->>'name' AS name
FROM kyc
WHERE details @> '{"status": "active"}';
-- Returns all customers where status is active
-- Uses GIN index, no full table scan
-- Key existence query
SELECT customer_id FROM kyc
WHERE details ? 'pan';
-- Returns customers who have a PAN field
-- Expression index for a specific field
CREATE INDEX idx_kyc_status ON kyc ((details->>'status'));
SELECT * FROM kyc WHERE details->>'status' = 'active';
How is json different from jsonb?
| Aspect | json | jsonb |
|---|---|---|
| Storage | Raw text | Binary tree |
| Whitespace | Preserved | Discarded |
| Key order | Preserved | Not preserved |
| Duplicate keys | Preserved | Last value wins |
| Parse cost | Re-parsed on every access | Parsed once on insert |
| GIN indexing | Not supported | Supported |
| Containment @> | Not supported | Supported |
| Insert speed | Faster (no parse) | Slightly slower |
-- json: preserves exact text (good for audit/legal)
SELECT details FROM kyc_raw WHERE customer_id = 'C001';
-- {"name": "Rajesh Kumar", "status": "active"}
-- Note: extra whitespace preserved
-- jsonb: normalized binary (good for queries)
SELECT details FROM kyc WHERE customer_id = 'C001';
-- {"name": "Rajesh Kumar", "pan": "ABCDE1234F", "status": "active"}
-- Whitespace normalized, keys may be reordered
How do you use JSON in Java with JDBC?
Rajesh reads and writes jsonb using JDBC with PGobject for PostgreSQL.
package com.bank.repository;
import java.sql.*;
import java.util.logging.Logger;
import org.postgresql.util.PGobject;
public class KycRepository {
private static final Logger LOG = Logger.getLogger(
KycRepository.class.getName());
private final Connection connection;
public KycRepository(Connection connection) {
this.connection = connection;
}
public void saveKyc(String customerId, String jsonDetails)
throws SQLException {
String sql = "INSERT INTO kyc (customer_id, details) VALUES (?, ?::jsonb)"
+ " ON CONFLICT (customer_id) DO UPDATE SET details = ?::jsonb";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, customerId);
ps.setString(2, jsonDetails);
ps.setString(3, jsonDetails);
ps.executeUpdate();
LOG.info("Saved KYC for " + customerId);
}
}
public String getKycField(String customerId, String field)
throws SQLException {
String sql = "SELECT details->>? AS value FROM kyc WHERE customer_id = ?";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, field);
ps.setString(2, customerId);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return rs.getString("value");
}
}
}
return null;
}
}
Output:
Saved KYC for C001
getKycField("C001", "name") → "Rajesh Kumar"
What are common mistakes with JSON columns?
A common mistake is using json instead of jsonb and then wondering why queries are slow and containment operators fail. Another mistake is storing structured, queryable data in JSON when proper columns would be better.
-- Wrong: using json and trying containment
SELECT * FROM kyc_raw
WHERE details @> '{"status": "active"}';
-- ERROR: operator does not exist: json @> json
-- Fix: use jsonb, not json
-- Anti-pattern: storing structured data as JSON
-- If you always query by status and city,
-- those should be proper indexed columns, not buried in JSON.
When should you use json vs jsonb?
Use jsonb almost always because it is the only form that supports indexing, containment, and equality. Use json only when the business or legal requirement is to return the customer's submitted bytes verbatim, since jsonb discards whitespace, key order, and duplicate keys.
Decision Guide: Need to query inside the JSON? ──► jsonb Need GIN index for fast lookups? ──► jsonb Need to preserve exact formatting? ──► json Need to return original submission?──► json (or store raw text separately) Default choice: ──► jsonb
Visual Overview
┌──────────────────────────────────────────────────────┐
│ json vs jsonb in PostgreSQL │
├──────────────────────────────────────────────────────┤
│ │
│ INSERT '{"name": "Rajesh", "status": "active"}' │
│ │ │ │
│ ▼ ▼ │
│ ┌─────────────┐ ┌─────────────┐ │
│ │ json │ │ jsonb │ │
│ │ Raw text │ │ Binary tree │ │
│ │ Preserved: │ │ Parsed once │ │
│ │ whitespace │ │ Keys sorted │ │
│ │ key order │ │ Deduped │ │
│ └──────┬──────┘ └──────┬──────┘ │
│ │ │ │
│ Every access: Stored binary: │
│ re-parse text fast access │
│ │ │ │
│ No indexing GIN index ✓ │
│ No @> operator @> containment ✓ │
│ No ? existence ? existence ✓ │
│ │
│ MySQL JSON: │
│ Single binary type, queried with: │
│ JSON_EXTRACT(col, '$.key') │
│ col->'$.key' (JSON value) │
│ col->>'$.key' (unquoted text) │
└──────────────────────────────────────────────────────┘
Key Takeaway
Modern databases treat JSON as a first-class column type. In PostgreSQL, jsonb is the right choice for almost every use case: it parses the JSON once into a binary form, supports GIN indexes, and offers containment with @> and key existence with ?. The json type stores raw text and preserves formatting, but re-parses on every access and cannot be indexed.
MySQL has a single binary JSON type queried with JSON_EXTRACT or the arrow operators, where -> returns a JSON value and ->> returns unquoted text. The double-arrow operator is the one you use in WHERE clauses and comparisons.
At the National Bank of India, Rajesh stores flexible KYC attributes in a jsonb column because the fields vary by customer and change often. A GIN index on the column makes containment queries fast: finding all active customers uses the index instead of scanning every row. Priya's KYC has different fields than Rajesh's, and both fit cleanly in the same column without hundreds of sparse nullable columns.
The senior nuance is that jsonb discards whitespace, key order, and duplicate keys. If the legal team requires the bank to return the customer's original submitted document byte-for-byte, you either store it as json or keep the raw text in a separate column alongside the queryable jsonb version.
Remember one sentence: use jsonb when you query inside the document because it is indexable, and use json only when you must preserve the exact original text.
---