JSON in Modern Databases: PostgreSQL, MongoDB, and Beyond

JSON support in databases has evolved significantly. Most modern databases now offer native JSON capabilities, but they handle it differently.

PostgreSQL JSON Support

PostgreSQL offers two JSON data types:

JSON — Stored as text, parsed on read

JSONB — Binary format, indexed, faster queries

-- Create table with JSONB column
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  attributes JSONB
);
-- Query nested JSON
SELECT * FROM products
WHERE attributes->>'color' = 'blue';
-- Index JSON fields
CREATE INDEX idx_color ON products ((attributes->>'color'));

JSONB Operators

-> — Get JSON object field (as JSON)

->> — Get JSON object field (as text)

#> — Get at path (as JSON)

@> — Contains

? — Key exists

MongoDB: Document Database

MongoDB stores data as BSON (Binary JSON):

{
  "_id": "ObjectId('...')",
  "name": "Wireless Headphones",
  "price": 79.99,
  "specs": {
    "battery": "30 hours",
    "bluetooth": "5.3"
  },
  "tags": ["audio", "wireless"]
}

MongoDB JSON Queries

db.products.find({
  "specs.bluetooth": "5.3",
  price: { $lt: 100 }
});

MySQL JSON Functions

-- Extract value
SELECT JSON_EXTRACT(data, '$.name') FROM users;
-- Shorthand
SELECT data->>'$.name' FROM users;
-- Create JSON
SELECT JSON_OBJECT('name', 'Alice', 'age', 30);

Choosing the Right Approach

PostgreSQL JSONB — Best for structured data with occasional JSON fields

MongoDB — Best for schemaless, document-oriented data

SQLite JSON — Great for embedded/local apps

Redis — Best for JSON caching with fast access

Export your database results to CSV with our JSON to CSV converter.