JSON in Databases: PostgreSQL JSONB and MongoDB

PostgreSQL stores and indexes JSON inside relational tables, and MongoDB is built entirely around JSON-like documents. In this lesson you will create JSONB columns, query, update and index them with PostgreSQL's operators, see how MongoDB stores and queries documents, and learn when JSON belongs in a database and when ordinary columns are better.

json vs jsonb in PostgreSQL

PostgreSQL has two JSON types. json stores the original text verbatim; jsonb parses it into a binary form on insert, which is what you want almost always:

| Aspect | json | jsonb | |--------|--------|---------| | Storage | exact text, key order preserved | binary, normalized, last duplicate key wins | | Insert speed | faster (no parsing) | slightly slower | | Query speed | re-parses on every access | fast, indexable | | Operators | ->, ->> | all, including @>, ?, ||, - |

Use json only when the exact bytes matter, such as an audit log of raw requests; otherwise use jsonb.

Querying JSONB

Two operators do most of the work: -> extracts a value as jsonb (for further navigation) and ->> extracts it as text (for comparison or display). @> asks whether the left document contains the right one, and ? checks whether a key or array element exists:

sql

SELECT name FROM products WHERE attrs->>'brand' = 'Dell';
SELECT name FROM products WHERE attrs @> '{"tags": ["office"]}';   -- array containment
SELECT name FROM products WHERE attrs ? 'size_in';                  -- key exists
SELECT name FROM products WHERE jsonb_path_exists(attrs, '$.ram_gb ? (@ >= 16)');

The last query uses SQL/JSON path, a JSONPath-like language built into PostgreSQL. Values extracted with ->> are text, so cast before numeric comparison: (attrs->>'ram_gb')::int >= 16. Updates work on the whole document or on a path:

sql

UPDATE products SET attrs = attrs || '{"warranty_years": 2}' WHERE id = 1;   -- merge keys
UPDATE products SET attrs = jsonb_set(attrs, '{ram_gb}', '32') WHERE id = 1; -- set one path
UPDATE products SET attrs = attrs - 'tags' WHERE id = 2;                     -- delete a key

Going the other way, SELECT json_agg(p) FROM products p; turns ordinary rows into one JSON array.

Indexing JSONB

Without an index every JSON query scans the table. A GIN index on the whole column accelerates @>, ?, ?| and ?&; the jsonb_path_ops variant is smaller but supports only containment. For one hot key, an expression B-tree index is best because it also supports ordering and ranges:

sql

CREATE INDEX products_attrs_gin  ON products USING GIN (attrs);
CREATE INDEX products_attrs_path ON products USING GIN (attrs jsonb_path_ops);
CREATE INDEX products_brand      ON products ((attrs->>'brand'));

The expression index is used only when a query repeats the exact expression. From Node.js the pg driver returns jsonb columns already parsed; pass JSON.stringify(value) when inserting.

MongoDB: A Database Made of Documents

MongoDB stores each record as a document in BSON, a binary superset of JSON that adds Date, ObjectId, 64-bit integers, Decimal128 and binary data. Queries, projections and updates are themselves JSON-like objects, and nested fields use dot notation:

javascript

const products = db.collection("products");

await products.insertOne({
  name: "Laptop",
  attrs: { brand: "Lenovo", ram_gb: 16, tags: ["work", "portable"] },
  createdAt: new Date()
});

const docs = await products
  .find({ "attrs.brand": "Lenovo", "attrs.ram_gb": { $gte: 16 } })
  .project({ name: 1, "attrs.ram_gb": 1 })
  .toArray();

await products.updateOne({ name: "Laptop" }, { $set: { "attrs.ram_gb": 32 } });
await products.createIndex({ "attrs.brand": 1 });

Because BSON has more types than JSON, exports use MongoDB's Extended JSON, which tags them: {"$oid": "66f1..."} for an ObjectId, {"$date": "2026-09-27T10:00:00Z"} for a Date. mongoexport and mongoimport use it so round trips preserve types.

Columns or JSON?

JSON in a database is a tool, not a substitute for schema design. It fits attributes that vary per row, payloads received from outside that you want to keep intact, and event data whose shape evolves. It fits poorly for fields you filter, join, sort or constrain constantly; those belong in typed columns. Most real tables are hybrids: core fields as columns, the long tail in one jsonb column.

Validate either way. PostgreSQL accepts CHECK (jsonb_typeof(attrs->'ram_gb') = 'number'), MongoDB collections can enforce a $jsonSchema validator, and application code can apply the JSON Schema from earlier in this course before data reaches the database.

Key Takeaways

Prefer jsonb over json in PostgreSQL; it is normalized, indexable and supports the full operator set.
-> navigates, ->> extracts text, @> tests containment, || merges, jsonb_set updates a path and - deletes a key.
Index jsonb with GIN for containment queries and with an expression B-tree for a single hot key.
MongoDB stores BSON documents, queries them with JSON-like filters and dot notation, and exports them as Extended JSON.
Keep frequently filtered or joined fields in typed columns; use JSON for variable or externally shaped data, and validate it.

Next lesson: JSON Web Tokens (JWT): How JSON Carries Identity — decode the JSON inside a JWT, understand its claims and signature, and avoid the classic mistakes.