JSON in Databases 2026: PostgreSQL JSONB vs MySQL JSON vs MongoDB Complete Guide

-- Create a table with typed columns + one JSONB column
CREATE TABLE products (
  id          BIGSERIAL    PRIMARY KEY,
  name        TEXT         NOT NULL,
  price_cents INTEGER      NOT NULL,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  metadata    JSONB        NOT NULL DEFAULT '{}'
);

-- Insert with nested JSONB document
INSERT INTO products (name, price_cents, metadata) VALUES
  ('MacBook Pro 14"', 199900, '{
    "brand": "Apple",
    "specs": { "ram_gb": 18, "storage_gb": 512, "chip": "M3 Pro" },
    "tags":  ["laptop", "pro", "m3"],
    "colors": ["silver", "space-black"]
  }'),
  ('Sony WH-1000XM5', 27999, '{
    "brand": "Sony",
    "specs": { "battery_hours": 30, "noise_cancellation": true },
    "tags":  ["headphones", "wireless"],
    "colors": ["black", "silver"]
  }');

-- ── Operators ──────────────────────────────────────────────────────────────
-- -> returns JSONB value by key (preserves type)
SELECT metadata -> 'brand'                    FROM products;  -- "Apple"

-- ->> returns text value by key
SELECT metadata ->> 'brand'                   FROM products;  -- Apple

-- -> then ->> for nested access
SELECT metadata -> 'specs' ->> 'chip'         FROM products;  -- M3 Pro

-- #>> path operator for deeply nested access
SELECT metadata #>> '{specs, ram_gb}'         FROM products;  -- 18

-- @> contains operator (uses GIN index — very fast)
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';

-- ? key exists operator
SELECT * FROM products WHERE metadata ? 'colors';

-- Array element contains
SELECT * FROM products WHERE metadata -> 'tags' ? 'laptop';

-- ── GIN index — enables fast @>, ?, ?|, ?& operators ─────────────────────
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- After GIN index: @> queries run in microseconds, not full table scans
SELECT * FROM products WHERE metadata @> '{"specs": {"noise_cancellation": true}}';

-- ── Functional index — for a specific frequently-queried field ─────────────
-- More selective than full GIN — useful when you always filter on one key
CREATE INDEX idx_products_brand
  ON products ((metadata ->> 'brand'));

SELECT * FROM products WHERE metadata ->> 'brand' = 'Apple';

-- ── Update a nested JSON key with jsonb_set ────────────────────────────────
-- jsonb_set(target, path, new_value, create_missing)
UPDATE products
  SET metadata = jsonb_set(metadata, '{specs, ram_gb}', '32')
  WHERE id = 1;

-- ── Append to a JSON array ─────────────────────────────────────────────────
UPDATE products
  SET metadata = jsonb_set(
    metadata,
    '{tags}',
    (metadata -> 'tags') || '["refurbished"]'::jsonb
  )
  WHERE id = 1;

-- ── Remove a key ──────────────────────────────────────────────────────────
UPDATE products
  SET metadata = metadata - 'colors'   -- remove top-level key
  WHERE id = 1;

-- ── Aggregate and expand JSON ──────────────────────────────────────────────
-- jsonb_each_text expands JSONB object into rows
SELECT key, value FROM products, jsonb_each_text(metadata -> 'specs') WHERE id = 1;

-- jsonb_array_elements expands JSON arrays into rows
SELECT tag FROM products, jsonb_array_elements_text(metadata -> 'tags') AS tag
WHERE metadata -> 'tags' ? 'laptop';

-- MySQL JSON column
CREATE TABLE products (
  id       BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(255)   NOT NULL,
  price    DECIMAL(10,2)  NOT NULL,
  metadata JSON           NOT NULL DEFAULT (JSON_OBJECT())
);

-- Insert
INSERT INTO products (name, price, metadata) VALUES
  ('MacBook Pro 14"', 1999.00, '{"brand":"Apple","specs":{"ram":18}}');

-- ── Operators ──────────────────────────────────────────────────────────────
-- -> returns JSON value (keeps type, returns JSON)
SELECT metadata -> '$.brand'                FROM products;   -- "Apple"

-- ->> returns unquoted string value
SELECT metadata ->> '$.brand'               FROM products;   -- Apple

-- Nested access with path expressions
SELECT metadata ->> '$.specs.ram'           FROM products;   -- 18

-- ── JSON functions ─────────────────────────────────────────────────────────
SELECT JSON_EXTRACT(metadata, '$.brand')    FROM products;   -- "Apple"
SELECT JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.brand')) FROM products;  -- Apple

-- JSON_CONTAINS
SELECT * FROM products WHERE JSON_CONTAINS(metadata, '"Apple"', '$.brand');

-- JSON_SET — update a field
UPDATE products SET metadata = JSON_SET(metadata, '$.specs.ram', 32) WHERE id = 1;

-- ── Generated column + index (MySQL's way to index JSON fields) ────────────
-- MySQL does not support GIN — index via a generated (virtual) column instead
ALTER TABLE products
  ADD COLUMN brand VARCHAR(100)
    GENERATED ALWAYS AS (metadata ->> '$.brand') VIRTUAL;

CREATE INDEX idx_products_brand ON products (brand);

-- Now WHERE brand = 'Apple' uses the index efficiently
SELECT * FROM products WHERE brand = 'Apple';

import { MongoClient } from 'mongodb';

const client = await MongoClient.connect(process.env.MONGO_URI);
const db     = client.db('shop');
const col    = db.collection('products');

// ── Insert a document ──────────────────────────────────────────────────────
await col.insertOne({
  name:  'MacBook Pro 14"',
  price: 1999.00,
  specs: { ram: 18, storage: 512, chip: 'M3 Pro' },
  tags:  ['laptop', 'pro', 'm3'],
});

// ── Query with dot notation for nested fields ──────────────────────────────
const appleProducts = await col.find({ brand: 'Apple' }).toArray();
const highRam       = await col.find({ 'specs.ram': { $gte: 16 } }).toArray();
const hasTag        = await col.find({ tags: 'pro' }).toArray(); // array contains

// ── Projection — return only specific fields ───────────────────────────────
const names = await col
  .find({ 'specs.ram': { $gte: 16 } })
  .project({ name: 1, price: 1, _id: 0 })
  .toArray();

// ── Update — $set modifies specific fields ─────────────────────────────────
await col.updateOne(
  { name: 'MacBook Pro 14"' },
  { $set: { 'specs.ram': 32, updatedAt: new Date() } }
);

// ── Push to an array field ─────────────────────────────────────────────────
await col.updateOne(
  { name: 'MacBook Pro 14"' },
  { $push: { tags: 'refurbished' } }
);

// ── Index on nested field ──────────────────────────────────────────────────
await col.createIndex({ 'specs.ram': 1 });          // single field
await col.createIndex({ brand: 1, price: -1 });     // compound
await col.createIndex({ tags: 1 });                  // multikey (array field)

// ── Aggregation pipeline ───────────────────────────────────────────────────
const summary = await col.aggregate([
  { $match:  { 'specs.ram': { $gte: 16 } } },
  { $group:  { _id: '$brand', avgPrice: { $avg: '$price' }, count: { $sum: 1 } } },
  { $sort:   { avgPrice: -1 } },
  { $limit:  10 },
]).toArray();