prisma-pg-json

Type-safe PostgreSQL JSON query builder for Prisma

Overview

A universal query builder that extends Prisma's capabilities while preserving familiar WHERE and ORDER BY API. Generate parameterized SQL queries for PostgreSQL through $queryRaw with advanced JSON/JSONB operations that standard Prisma doesn't support.

Goal: Replicate Prisma's query interface using $queryRaw while adding powerful JSON functionality.

Features

🎯 Prisma-compatible API - Familiar where and orderBy syntax from Prisma
🔍 JSON Path queries - Access nested data with ['key', 'nested'] or wildcard patterns
🌟 Wildcard support - Query arrays with *, array indices [0], [-1]
🚀 JSON sorting - Order by nested JSON fields with type casting and aggregations
🔧 Extended filters - All Prisma filters plus advanced JSON operations
🔎 Full-text search - PostgreSQL FTS in JSON with language support and phrase matching
🎨 Logical operators - AND, OR, NOT combinations just like Prisma
📦 Prisma native - Uses Prisma.Sql for safe parameterized queries
⚡ Universal - Works with any table structure, not schema-dependent

Prisma vs prisma-pg-json

Feature	Standard Prisma	This Library
Basic filters	`contains`, `gt`, `gte`, `in`, etc.	✅ Same API
Logical operators	`AND`, `OR`, `NOT`	✅ Same API
JSON field access	`metadata: { path: ['key'], equals: 'value' }`	✅ Same + wildcards
JSON array queries	Limited	✅ `path: ['tags', '*', 'name']`
Array indices	❌ Not supported	✅ `path: ['items', 0]` or `'items[0]'`
Negative indices	❌ Not supported	✅ `path: ['items', -1]` or `'items[-1]'`
JSON sorting	❌ Not supported	✅ With type casting and aggregations
Full-text search	❌ Not in JSON	✅ PostgreSQL FTS with language support
Wildcard paths	❌ Not supported	✅ `'tags[*].name'` syntax
Path formats	Array only	✅ Array `['a', 'b']` or string `'a.b'`
Query method	ORM methods	`$queryRaw` with generated SQL

Installation

npm install @revisium/prisma-pg-json

Usage

Setup

Before using the library, you must configure it with your Prisma client:

import { configurePrisma } from '@revisium/prisma-pg-json';
import { Prisma } from '@prisma/client';

// Configure once at application startup
configurePrisma(Prisma);

Important: Call configurePrisma() before using any query building functions. This allows the library to work with any Prisma client version and custom import paths.

Core Functions

The library provides two main functions for building WHERE and ORDER BY clauses:

import { generateWhere, generateOrderBy, FieldConfig } from '@revisium/prisma-pg-json';
import { PrismaClient, Prisma } from '@prisma/client';
import { configurePrisma } from '@revisium/prisma-pg-json';

// Configure the library
configurePrisma(Prisma);

const prisma = new PrismaClient();

// Define your field types (FieldConfig maps field names to types)
const fieldConfig: FieldConfig = {
  id: 'string',        // FieldType = 'string' | 'number' | 'boolean' | 'date' | 'json'
  name: 'string',
  age: 'number',
  isActive: 'boolean',
  createdAt: 'date',
  metadata: 'json',
};

// Generate WHERE clause
const whereClause = generateWhere({
  where: {
    name: { contains: 'john', mode: 'insensitive' },
    age: { gte: 18 },
  },
  fieldConfig,
  tableAlias: 'u',
});

// Generate ORDER BY clause
const orderByClause = generateOrderBy({
  orderBy: [
    { name: 'asc' },
    { createdAt: 'desc' }
  ],
  fieldConfig,
  tableAlias: 'u',
});

// Use in raw SQL query
const query = Prisma.sql`
  SELECT * FROM users u
  WHERE ${whereClause}
  ${orderByClause}
`;

const results = await prisma.$queryRaw(query);

JSON Path Queries

Access nested JSON data with flexible path syntax - use string or array notation:

const whereClause = generateWhere({
  where: {
    // Path as array (recommended for complex paths)
    metadata: {
      path: ['settings', 'theme'],
      equals: 'dark'
    },

    // Path as string with dot notation
    metadata: {
      path: 'user.preferences.language',
      equals: 'en'
    },

    // Array wildcard - matches any array element
    metadata: {
      path: ['tags', '*', 'name'],
      string_contains: 'important'
    },

    // String path with wildcards
    metadata: {
      path: 'items[*].status',
      equals: 'active'
    },

    // Array indices with array notation
    metadata: {
      path: ['items', 0, 'status'],
      equals: 'active'
    },

    // Array indices with string notation
    metadata: {
      path: 'items[0].price',
      gte: 100
    },

    // Negative indices (from end)
    metadata: {
      path: ['history', -1, 'action'],
      equals: 'created'
    },

    // Negative indices with string notation
    metadata: {
      path: 'reviews[-1].rating',
      gte: 4
    },

    // Array contains object
    metadata: {
      path: ['reviews'],
      array_contains: { rating: 5, verified: true }
    }
  },
  fieldConfig: { metadata: 'json' },
  tableAlias: 't'
});

JSON Sorting

Sort by nested JSON fields with type casting and aggregations. When sorting by JSON arrays, use aggregations to determine sort value:

Supported types: text, int, float, boolean, timestamp Supported aggregations (for JSON arrays): min, max, avg, first, last

const orderByClause = generateOrderBy({
  orderBy: [
    // Sort by nested JSON number (array path)
    {
      metadata: {
        path: ['stats', 'score'],
        direction: 'desc',
        type: 'float'
      }
    },

    // Sort by nested JSON date (string path)
    {
      metadata: {
        path: 'timestamps.lastActivity',
        direction: 'desc',
        type: 'timestamp'
      }
    },

    // Sort by array element (array notation)
    {
      metadata: {
        path: ['priorities', 0],
        direction: 'asc',
        type: 'int'
      }
    },

    // Sort by array element (string notation)
    {
      metadata: {
        path: 'ratings[0].value',
        direction: 'desc',
        type: 'float'
      }
    },

    // Sort by array aggregation - average
    {
      metadata: {
        path: 'scores',
        direction: 'desc',
        type: 'float',
        aggregation: 'avg'
      }
    },

    // Sort by array aggregation - max value
    {
      metadata: {
        path: ['reviews', '*', 'rating'],
        direction: 'desc',
        type: 'int',
        aggregation: 'max'
      }
    },

    // Sort by first element
    {
      metadata: {
        path: 'tags',
        direction: 'desc',
        aggregation: 'first'
      }
    },

    // Sort by last element
    {
      metadata: {
        path: ['history', '*', 'timestamp'],
        direction: 'desc',
        type: 'timestamp',
        aggregation: 'last'
      }
    }
  ],
  fieldConfig: { metadata: 'json' },
  tableAlias: 't'
});

Filter Types

Complete list of available filters for each field type:

String Filters

name: {
  equals: 'john',              // Exact match
  not: 'admin',                // Not equal
  contains: 'john',            // Contains substring
  startsWith: 'mr',            // Starts with
  endsWith: 'gmail.com',       // Ends with
  in: ['active', 'pending'],   // In array
  notIn: ['deleted', 'banned'], // Not in array
  mode: 'insensitive'          // Case-insensitive (works with contains, startsWith, endsWith)
}

Number Filters

age: {
  equals: 25,                  // Exact match
  not: 0,                      // Not equal
  gt: 18,                      // Greater than
  gte: 18,                     // Greater than or equal
  lt: 65,                      // Less than
  lte: 65,                     // Less than or equal
  in: [18, 25, 30],           // In array
  notIn: [0, 999]             // Not in array
}

Boolean Filters

isActive: true,                // Direct boolean value
isDeleted: {
  equals: false,               // Explicit equals
  not: true                    // Not equal
}

Date Filters

createdAt: {
  equals: new Date('2024-01-01'),  // Exact date
  not: new Date('2024-01-01'),     // Not equal
  gt: new Date('2024-01-01'),      // After date
  gte: new Date('2024-01-01'),     // After or equal
  lt: new Date('2025-01-01'),      // Before date
  lte: new Date('2025-01-01'),     // Before or equal
  in: [date1, date2],              // In array
  notIn: [date3, date4]            // Not in array
}

JSON Filters

metadata: {
  path: ['config', 'enabled'],     // Required: JSON path (string | string[])

  // Value comparisons
  equals: true,                    // Exact match
  not: false,                      // Not equal

  // String operations (on JSON strings)
  string_contains: 'text',         // Contains substring
  string_starts_with: 'prefix',   // Starts with
  string_ends_with: 'suffix',     // Ends with

  // Full-text search (PostgreSQL FTS)
  search: 'database performance',  // Full-text search with stemming
  searchLanguage: 'english',       // Language config ('simple', 'english', 'russian', etc.)
  searchType: 'plain',             // 'plain' (AND) or 'phrase' (exact phrase)
  searchIn: 'values',              // 'all' (default), 'values', 'keys', 'strings', 'numbers', 'booleans'

  // Number/Date operations (on JSON numbers/dates)
  gt: 10,                          // Greater than
  gte: 10,                         // Greater than or equal
  lt: 100,                         // Less than
  lte: 100,                        // Less than or equal

  // Array operations (for JSON arrays)
  array_contains: ['value1', 'value2'],    // Array contains ALL specified elements (always array)
  array_starts_with: 'first_value',       // First array element equals value
  array_ends_with: 'last_value',          // Last array element equals value

  // Array/object membership
  in: ['value1', 'value2'],        // Value in array
  notIn: ['value3', 'value4'],     // Value not in array

  // Case sensitivity (works with string operations)
  mode: 'insensitive'              // Case-insensitive matching
}

Full-Text Search in JSON

PostgreSQL full-text search (FTS) with support for different languages, search types, and recursive path searching:

const whereClause = generateWhere({
  where: {
    // Search across entire JSON document (root level)
    metadata: {
      path: '',  // or path: []
      search: 'postgresql database',
    },

    // Search in specific field
    metadata: {
      path: 'content',
      search: 'machine learning',
    },

    // Search in nested field
    metadata: {
      path: 'article.body',
      search: 'artificial intelligence',
    },

    // Search in array (searches all elements recursively)
    metadata: {
      path: 'tags',
      search: 'javascript',
    },

    // Search with specific language (better stemming)
    metadata: {
      path: 'description',
      search: 'running quickly',
      searchLanguage: 'english',  // 'simple', 'english', 'russian', 'french', etc.
    },

    // Phrase search (exact phrase match)
    metadata: {
      path: 'text',
      search: 'full-text search',
      searchType: 'phrase',  // words must appear in this exact order
    },

    // Plain search (default - all words must be present, AND logic)
    metadata: {
      path: 'content',
      search: 'database performance',
      searchType: 'plain',  // both 'database' AND 'performance' must be present
    },

    // Search only in values (exclude JSON keys from search)
    metadata: {
      path: '',
      search: 'Anton',
      searchIn: 'values',  // searches only values, not field names
    },

    // Search only in JSON keys (field names)
    metadata: {
      path: '',
      search: 'username',
      searchIn: 'keys',  // searches only field names
    },

    // Search only in string values
    metadata: {
      path: 'data',
      search: 'text',
      searchIn: 'strings',  // only string values
    },

    // Search only in numeric values
    metadata: {
      path: 'stats',
      search: '42',
      searchIn: 'numbers',  // only numeric values
    },

    // Search only in boolean values
    metadata: {
      path: 'flags',
      search: 'true',
      searchIn: 'booleans',  // only boolean values
    },
  },
  fieldConfig: { metadata: 'json' },
  tableAlias: 't'
});

Search Behavior

Search Types:

plain (default): Uses plainto_tsquery - all words must be present (AND logic)
- 'database performance' → finds docs with both "database" AND "performance"
phrase: Uses phraseto_tsquery - exact phrase in order
- 'full text search' → finds only exact phrase "full text search"

Search Languages:

'simple' (default): No stemming, works with any language (UTF-8)
'english': English stemming (running → run)
'russian': Russian stemming
'french', 'german', 'spanish', etc.: Language-specific stemming

Search Scope (searchIn):

'all' (default): Searches in JSON keys + all values (strings, numbers, booleans)
'values': Searches only in values (strings + numbers + booleans), excludes field names
'keys': Searches only in JSON field names, excludes values
'strings': Searches only in string values
'numbers': Searches only in numeric values
'booleans': Searches only in boolean values (true/false)

Note: null values are not indexed by PostgreSQL FTS and cannot be searched.

Path Behavior:

Searches are always recursive - searches the specified path and all nested levels
Empty path '' or []: searches entire JSON document
Specific path 'content': searches in that field and all nested structures
Array path 'tags': searches across all array elements recursively

Examples

// Find documents about "PostgreSQL" OR "MySQL" - use OR operator
{
  OR: [
    { metadata: { path: '', search: 'PostgreSQL' } },
    { metadata: { path: '', search: 'MySQL' } }
  ]
}

// Find documents with "database" in title AND "performance" in content
{
  AND: [
    { metadata: { path: 'title', search: 'database' } },
    { metadata: { path: 'content', search: 'performance' } }
  ]
}

// Combine FTS with other filters
{
  AND: [
    { metadata: { path: '', search: 'javascript' } },
    { metadata: { path: 'published', equals: true } },
    { metadata: { path: 'rating', gte: 4 } }
  ]
}

Logical Operators

Combine filters with AND, OR, NOT:

const whereClause = generateWhere({
  where: {
    AND: [
      { age: { gte: 18 } },
      {
        OR: [
          { status: 'active' },
          { priority: { gte: 5 } }
        ]
      }
    ],
    NOT: {
      email: { contains: 'spam' }
    }
  },
  fieldConfig,
  tableAlias: 'u'
});

Advantages over Standard Prisma

JSON Path Operations

Standard Prisma only supports simple JSON access. This library provides:

Wildcard queries: tags[*].name to search all array elements
Array indices: items[0], items[-1] for specific positions
Deep nesting: Complex paths like ['user', 'settings', 'preferences', 'theme']
Array operations: Check if arrays contain specific objects

JSON Sorting

Prisma doesn't support sorting by JSON fields. This library enables:

Sort by nested JSON values with proper type casting
Multiple JSON sorts with different types (numeric, text, date)
Array element sorting

Keyset Pagination

Standard OFFSET-based pagination loses rows when data has duplicate sort values or when rows are inserted/deleted between pages. Keyset (cursor-based) pagination solves this by using the last seen row's values to determine the starting point for the next page.

The library provides utilities for building keyset pagination with multi-column sort support:

Cursor Encoding / Decoding

import {
  Prisma,
  encodeCursor,
  decodeCursor,
  computeSortHash,
  extractCursorValues,
  OrderByPart,
} from '@revisium/prisma-pg-json';

// OrderByPart describes each sort column
const parts: OrderByPart[] = [
  {
    expression: Prisma.sql`r."createdAt"`,
    direction: 'DESC',
    fieldName: 'createdAt',
    isJson: false,
  },
];

// Compute a hash of the sort configuration (detects sort changes between pages)
const sortHash = computeSortHash(parts);

// Extract cursor values from a row
const row = { createdAt: new Date('2025-01-01'), versionId: 'v-123' };
const values = extractCursorValues(row, parts);
// → ['2025-01-01T00:00:00.000Z']

// Encode into an opaque cursor string
const cursor = encodeCursor(values, 'v-123', sortHash);

// Decode back
const decoded = decodeCursor(cursor);
// → { values: ['2025-01-01T00:00:00.000Z'], tiebreaker: 'v-123', sortHash: '...' }

Building the WHERE Condition

buildKeysetCondition generates a multi-column WHERE clause that skips past the cursor position. It handles mixed ASC/DESC directions and NULL values:

import { buildKeysetCondition } from '@revisium/prisma-pg-json';

const condition = buildKeysetCondition(
  parts,                              // OrderByPart[] — sort columns
  decoded.values,                     // cursor values from decodeCursor
  decoded.tiebreaker,                 // tiebreaker value (e.g., row ID)
  Prisma.sql`r."versionId"`,          // tiebreaker SQL expression
);

// Use in your query
const query = Prisma.sql`
  SELECT * FROM rows r
  WHERE r."tableVersionId" = ${tableVersionId}
    AND ${condition}
  ORDER BY r."createdAt" DESC, r."versionId" DESC
  LIMIT ${take + 1}
`;

Using with generateOrderByParts

generateOrderByParts (from the ORDER BY module) produces the OrderByPart[] array needed by the keyset utilities:

import { generateOrderByParts } from '@revisium/prisma-pg-json';

const parts = generateOrderByParts({
  tableAlias: 'r',
  orderBy: [{ createdAt: 'desc' }, { data: { path: 'priority', type: 'int', direction: 'asc' } }],
  fieldConfig: { createdAt: 'date', data: 'json' },
});

const sortHash = computeSortHash(parts);
const cursorValues = extractCursorValues(lastRow, parts);
const cursor = encodeCursor(cursorValues, lastRow.id, sortHash);

Universal Design

Works with any table structure - just define your field types and start querying.

License

MIT

Name		Name	Last commit message	Last commit date
Latest commit History 47 Commits
.github/workflows		.github/workflows
prisma		prisma
src		src
.env.example		.env.example
.gitignore		.gitignore
.nvmrc		.nvmrc
.prettierignore		.prettierignore
.prettierrc		.prettierrc
CLAUDE.md		CLAUDE.md
LICENSE		LICENSE
README.md		README.md
codecov.yml		codecov.yml
docker-compose.yml		docker-compose.yml
eslint.config.mjs		eslint.config.mjs
package-lock.json		package-lock.json
package.json		package.json
prisma.config.ts		prisma.config.ts
sonar-project.properties		sonar-project.properties
tsconfig.json		tsconfig.json

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

prisma-pg-json

Overview

Features

Prisma vs prisma-pg-json

Installation

Usage

Setup

Core Functions

JSON Path Queries

JSON Sorting

Filter Types

String Filters

Number Filters

Boolean Filters

Date Filters

JSON Filters

Full-Text Search in JSON

Search Behavior

Examples

Logical Operators

Advantages over Standard Prisma

JSON Path Operations

JSON Sorting

Keyset Pagination

Cursor Encoding / Decoding

Building the WHERE Condition

Using with generateOrderByParts

Universal Design

License

About

Uh oh!

Releases 16

Contributors

Uh oh!

Languages

Folders and files

Latest commit

History

Repository files navigation

prisma-pg-json

Overview

Features

Prisma vs prisma-pg-json

Installation

Usage

Setup

Core Functions

JSON Path Queries

JSON Sorting

Filter Types

String Filters

Number Filters

Boolean Filters

Date Filters

JSON Filters

Full-Text Search in JSON

Search Behavior

Examples

Logical Operators

Advantages over Standard Prisma

JSON Path Operations

JSON Sorting

Keyset Pagination

Cursor Encoding / Decoding

Building the WHERE Condition

Using with generateOrderByParts

Universal Design

License

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases 16

Contributors

Uh oh!

Languages