Skip to content

JSON fields

Every app eventually stores structured data inside a single column — a metadata blob, a settings object, an array of tags. Cascade gives you a unified set of query methods for reaching into those shapes on both MongoDB documents (where everything is JSON-shaped already) and Postgres jsonb columns.

The mental model is one sentence: paths walk the structure. profile.address.city walks profile → address → city. Same syntax on both drivers; Cascade translates to the native operator at execution time.

This page is about the read side — filtering, projecting, inspecting JSON paths. Writing nested fields is just .set("profile.address.city", "NYC") and is covered in CRUD basics.

Before you reach for these — .where() already does dot-notation

Section titled “Before you reach for these — .where() already does dot-notation”

Worth stating loudly, because it’s the most common case: the regular .where() family supports dot-notation paths today. Same with .set(), .get(), .orderBy(), .select(). For simple equality or operator-based filters on nested fields, you don’t need any of the helpers on this page:

await User.where("profile.address.city", "NYC").get();
await User.where("preferences.theme", "!=", "default").get();
await User.query().orderBy("profile.lastLogin", "desc").get();

The helpers in this guide earn their place by doing JSON-specific operations — array containment, key existence, length, type checks. For everything else, the regular query vocabulary already covers it.

On PostgreSQL, a string, number or boolean saved into a json / jsonb column is stored as that JSON value ("hello" becomes the JSON string "hello"), the same way objects and arrays already are. The driver reads the column types from the database when it connects, so nothing needs configuring. Likewise, an INTEGER[] (or any native array) column receives your number array as a real array, not as a vector literal.

Dot-notation paths walk the structure:

PathMongoDBPostgres JSONB
profile.address.cityprofile.address.cityprofile -> 'address' ->> 'city'
tagsarray fieldjsonb array column
tags.0element 0 of the arraytags -> 0

You don’t write the right-hand column. You write the dot-path. Cascade emits the right thing per driver.

whereJsonContains — value present in array or object

Section titled “whereJsonContains — value present in array or object”
const taggedJS = await Post.query().whereJsonContains("tags", "javascript").get();
const admins = await User.query().whereJsonContains("profile.roles", "admin").get();

Tests whether the JSON value at the path contains the given value. Works on array fields (tags) and on object subtrees alike.

const notDeprecated = await Post.query().whereJsonDoesntContain("tags", "deprecated").get();

The negation. Same shape, opposite outcome.

whereJsonContainsKey — the key is present

Section titled “whereJsonContainsKey — the key is present”
const withAvatar = await User.query().whereJsonContainsKey("profile.avatar").get();

Tests whether the path exists (the key is present), regardless of its value. Useful for “users who have configured an avatar” versus “users whose avatar happens to be null” — those are different questions.

const verbose = await Post.query().whereJsonLength("tags", ">", 5).get();

An operator-style filter on the length of an array (or count of keys in an object) at the path. Operators are the same vocabulary as .where(): =, !=, >, >=, <, <=.

whereJsonIsArray / whereJsonIsObject — type checks

Section titled “whereJsonIsArray / whereJsonIsObject — type checks”
await User.query().whereJsonIsArray("profile.roles").get();
await User.query().whereJsonIsObject("settings").get();

Assert the path resolves to an array (or object). Useful for sanity checks in loosely-shaped JSON where the same field might hold different shapes across rows.

When the column is an array (not just a JSON path that happens to point at one), Cascade exposes a parallel family that reads more naturally:

// Plain value in an array column
await Post.query().whereArrayContains("tags", "javascript").get();
// Match by key inside an array of objects
await Order.query().whereArrayContains("items", "laptop", "name").get();
// Negation
await User.query().whereArrayNotContains("blocked", userId).get();
// "Has the value OR is empty" — for permissive defaults
await User.query().whereArrayHasOrEmpty("permissions", "admin").get();
await User.query().whereArrayNotHaveOrEmpty("blocked", spammerId).get();
// Array length with an operator
await Post.query().whereArrayLength("tags", ">=", 2).get();

:::tip — whereArray* vs whereJson*

They overlap on plain arrays. Use the whereArray* family when the column is an array — the names read better and the third key argument lets you match inside arrays of objects. Use whereJson* when you’re navigating a JSON structure that contains an array somewhere inside it.

:::

const compact = await User.query().selectJson("profile.address.city", "city").get();
// each row: { city: "..." }

Projects a nested JSON path into a top-level alias on each result row. Saves transferring the entire JSON column over the wire when you only need one nested value out of it.

query.selectJsonRaw("stats.views", { $ifNull: ["$stats.views", 0] }, "views");

Apply a driver-native expression to a JSON path under an alias. Useful for coalescing, defaults, or any computation that needs more than a plain projection — $ifNull on Mongo, COALESCE(...) on Postgres.

query.deselectJson("profile.ssn");

Exclude a nested JSON path from the projection. Useful for stripping sensitive nested data without rewriting the whole select.

Pulling filter + projection + the rest of the query API together:

const writers = await User.query()
.whereJsonContains("profile.roles", "writer")
.whereJsonLength("preferences.notifications", ">", 0)
.selectJson("profile.address.city", "city")
.select(["id", "name"])
.get();
// each row: { id, name, city }

In English: find users tagged as “writer” who have at least one notification preference, and return just their id, name, and city. One query. Both drivers. No driver-specific syntax in the call site.

If you’re reading existing raw SQL or Mongo queries and translating to Cascade (or the other way around), here’s the Rosetta:

Cascade helperMongoDBPostgres JSONB
whereJsonContains("tags", v){ tags: v } / $intags @> v::jsonb
whereJsonContainsKey("a.b"){ "a.b": { $exists: true } }jsonb_path_exists
whereJsonLength("tags", ">", 5)$expr + $sizejsonb_array_length
whereJsonIsArray("tags"){ tags: { $type: "array" } }jsonb_typeof(tags) = 'array'
selectJson("a.b", "alias")$project: { alias: "$a.b" }a -> 'b' AS alias

The takeaway: Cascade is the abstraction; the driver emits whatever its engine speaks.

Filtering on JSON paths gets enormously cheaper when the path is indexed. On MongoDB, a regular field index on a dot-path works (db.users.createIndex({ "profile.roles": 1 })). On Postgres, JSONB columns want a GIN index for containment queries (@>). Without an index, the database has to scan and decode every row.

If you’ve got a JSON path that’s hot in queries, declare an index for it. The migrations layer covers index creation; see the migrations guide once Cascade ships full index docs.

  • Writing nested JSON paths (.set("profile.address.city", "NYC")) — CRUD basics
  • Indexing JSON paths — migrations guide
  • Driver-specific JSONB / aggregation tricks beyond the structured helpers — expressions guide