JSONB Operations

PostgreSQL's JSONB is first-class in Zanith. Extract values, check containment, test key existence — all through typed field references.

#When to use JSONB

Not everything fits neatly into columns. User preferences, feature flags, form submissions, API responses — JSONB lets you store structured data without creating new tables for every shape. The tradeoff is less type safety (it's unknown by default), but more flexibility.

#Defining a JSONB field

11 lines


1const User = defineModel((m) => ({
2  name: 'User', table: 'users',
3  fields: {
4    ...m.id(),
5    email: m.string(),
6    // Untyped JSON (flexible)
7    settings: m.json().nullable(),
8    // Typed JSON (if you know the shape)
9    preferences: m.json<{ theme: string; language: string }>().nullable(),
10  },
11}));

#Text extraction (→>>)

Extract a text value from a JSON key. This is the most common operation — it returns the value as a string, which you can then compare.

10 lines


1// Find posts where metadata.type = 'article'
2const articles = await db.post.query()
3  .where(({ post }) =>
4    post.metadata.jsonText('type').eq('article')
5  )
6  .execute();
7 
8// Generated SQL:
9// WHERE "posts"."metadata"->>'type' = $1
10// Params: ['article']

#Containment (@>)

Check if a JSONB column contains a specific structure. This is powerful for filtering by nested values without extracting each key individually.

13 lines


1// Find posts with { featured: true } in their metadata
2const featured = await db.post.query()
3  .where(({ post }) =>
4    post.metadata.jsonContains({ featured: true })
5  )
6  .execute();
7 
8// Generated SQL:
9// WHERE "posts"."metadata" @> $1::jsonb
10// Params: [{ featured: true }]
11 
12// Works with nested structures too:
13post.metadata.jsonContains({ config: { priority: 'high' } })

#Key existence (?)

Check if a key exists in the JSON object — regardless of its value.

10 lines


1// Find posts that have a 'tags' key in their metadata
2const tagged = await db.post.query()
3  .where(({ post }) =>
4    post.metadata.jsonHasKey('tags')
5  )
6  .execute();
7 
8// Generated SQL:
9// WHERE "posts"."metadata" ? $1
10// Params: ['tags']

#Operator reference

Method	PostgreSQL operator	Use case
`.jsonText(key)`	`->> 'key'`	Extract text value, then compare with `.eq()`, `.contains()`, etc.
`.jsonPath(key)`	`-> 'key'`	Extract JSON value (not text) for nested access
`.jsonContains(obj)`	`@> obj::jsonb`	Check if JSON contains a structure
`.jsonHasKey(key)`	`? 'key'`	Check if a key exists in the JSON object

#Combining with other filters

12 lines


1// Published posts with type='article' and featured=true
2const results = await db.post.query()
3  .with({ author: true })
4  .where(({ post, author }) =>
5    and(
6      post.published.eq(true),
7      post.metadata.jsonText('type').eq('article'),
8      post.metadata.jsonContains({ featured: true }),
9      author.role.eq('ADMIN')
10    )
11  )
12  .execute();

JSONB filters work seamlessly with regular field filters, relational filters, and logical combinators (and, or, not).