@nam088/json-logic-to-sql

A powerful, secure, and type-safe library to convert JSON Logic rules into SQL WHERE clauses with comprehensive schema validation and multi-dialect support.

Features

Secure by Default: Generates parameterized SQL queries to prevent SQL injection attacks
Multi-Dialect Support: Works with PostgreSQL, MySQL, MSSQL (SQL Server), and SQLite
Type-Safe: Written in TypeScript with comprehensive type definitions and strict validation
Schema Validation: Validates rules against a defined schema before compilation with customizable constraints
Advanced Field Mapping: Support for column mapping, JSONB paths, and computed fields
Field Transformations: Built-in SQL transforms (LOWER, UPPER, TRIM, DATE functions, etc.)
Flexible Operators: Comprehensive operator support including comparison, logical, string, array, and JSONB operations
Query Utilities: Built-in helpers for pagination, sorting, and SELECT clause generation
Extensible: Support for custom transforms and field constraints

Installation

npm install @nam088/json-logic-to-sql

Quick Start

1. Define your Schema

Define the fields that are allowed to be queried with their types, operators, and optional constraints.

import { FilterSchema } from '@nam088/json-logic-to-sql';

const schema: FilterSchema = {
  fields: {
    firstName: {
      type: 'string',
      operators: ['eq', 'ne', 'like', 'in'],
      column: 'first_name', // Maps 'firstName' to 'first_name' column
    },
    age: {
      type: 'number',
      operators: ['eq', 'gt', 'lt', 'gte', 'lte', 'between'],
      constraints: {
        min: 0,
        max: 150,
      },
    },
    status: {
      type: 'string',
      operators: ['eq', 'in'],
      options: {
        items: [
          { value: 'active', label: 'Active' },
          { value: 'inactive', label: 'Inactive' },
          { value: 'pending', label: 'Pending' },
        ],
        strict: true, // Only allow predefined values
      },
    },
    email: {
      type: 'string',
      operators: ['eq', 'like', 'ilike'],
      constraints: {
        pattern: /^[^\s@]+@[^\s@]+\.[^\s@]+$/,
      },
    },
  },
  settings: {
    maxDepth: 5,
    maxConditions: 100,
  },
};

2. Initialize the Compiler

import { JsonLogicCompiler } from '@nam088/json-logic-to-sql';

const compiler = new JsonLogicCompiler({
  schema,
  dialect: 'postgresql', // or 'mysql', 'mssql', 'sqlite'
});

3. Compile Rules

const rule = {
  and: [
    { '==': [{ var: 'status' }, 'active'] },
    { '>': [{ var: 'age' }, 18] }
  ]
};

try {
  const result = compiler.compile(rule);
  console.log(result.sql);
  // Output: (status = $1 AND age > $2)
  
  console.log(result.params);
  // Output: { '$1': 'active', '$2': 18 }
} catch (error) {
  console.error('Compilation failed:', error.message);
}

Core Concepts

Field Schema

Each field in your schema can have:

{
  type: FieldType;              // Data type (string, number, boolean, date, array, etc.)
  operators: Operator[];        // Allowed operators for this field
  column?: string;              // Database column name (if different from field name)
  filterable?: boolean;         // Allow in WHERE clauses (default: true)
  selectable?: boolean;         // Allow in SELECT clauses (default: true)
  sortable?: boolean;           // Allow in ORDER BY (default: true)
  nullable?: boolean;           // Allow null values
  caseSensitive?: boolean;      // For string comparisons
  options?: OptionConfig;       // Predefined values
  constraints?: FieldConstraints; // Validation rules
  transform?: FieldTransform;   // SQL transformations
  jsonPath?: string;            // JSONB path for nested data
  meta?: Record<string, unknown>; // Custom metadata
}

Computed Fields

Define fields that are calculated from SQL expressions:

const schema: FilterSchema = {
  fields: {
    fullName: {
      type: 'string',
      operators: ['like', 'ilike'],
      computed: true,
      expression: "first_name || ' ' || last_name",
    },
    ageGroup: {
      type: 'string',
      operators: ['eq', 'in'],
      computed: true,
      expression: "CASE WHEN age < 18 THEN 'minor' ELSE 'adult' END",
    },
  },
};

Field Transformations

Apply SQL transformations to fields:

const schema: FilterSchema = {
  fields: {
    email: {
      type: 'string',
      operators: ['eq', 'like'],
      transform: {
        input: 'lower', // Apply LOWER() when filtering
        output: 'lower', // Apply LOWER() when selecting
      },
    },
    createdAt: {
      type: 'datetime',
      operators: ['eq', 'gt', 'lt'],
      transform: {
        input: 'date', // Extract date part for comparison
      },
    },
  },
};

Built-in transforms: lower, upper, trim, ltrim, rtrim, unaccent, date, year, month, day

Custom transforms:

transform: {
  input: {
    name: 'custom',
    sql: 'CUSTOM_FUNCTION({column})'
  }
}

Field Constraints

Validate values before compilation:

constraints: {
  // String constraints
  minLength: 3,
  maxLength: 100,
  pattern: /^[a-zA-Z]+$/,
  
  // Number constraints
  min: 0,
  max: 1000,
  
  // Array constraints
  minItems: 1,
  maxItems: 10,
  
  // Date constraints
  dateFormat: 'YYYY-MM-DD',
  minDate: '2020-01-01',
  maxDate: '2025-12-31',
  
  // Custom validation
  validate: (value) => {
    if (typeof value === 'string' && value.includes('admin')) {
      return 'Cannot filter by admin values';
    }
    return true;
  }
}

Supported Operators

Comparison Operators

JSON Logic	Internal	SQL	Description
`==`, `===`	`eq`	`=`	Equal
`!=`, `!==`	`ne`	`<>`	Not equal
`>`	`gt`	`>`	Greater than
`>=`	`gte`	`>=`	Greater than or equal
`<`	`lt`	`<`	Less than
`<=`	`lte`	`<=`	Less than or equal

Logical Operators

and - Logical AND
or - Logical OR
!, not - Logical NOT

String Operators

Operator	Description	Example
`like`	SQL LIKE (case-sensitive)	`{ like: [{ var: 'name' }, 'John%'] }`
`ilike`	Case-insensitive LIKE (PostgreSQL)	`{ ilike: [{ var: 'name' }, 'john%'] }`
`starts_with`	Starts with string	`{ starts_with: [{ var: 'name' }, 'John'] }`
`ends_with`	Ends with string	`{ ends_with: [{ var: 'email' }, '.com'] }`
`contains`	Contains substring	`{ contains: [{ var: 'description' }, 'urgent'] }`
`regex`	Regular expression match	`{ regex: [{ var: 'code' }, '^[A-Z]{3}'] }`

Array Operators

Operator	Description	Example
`in`	Value in list	`{ in: [{ var: 'status' }, ['active', 'pending']] }`
`not_in`	Value not in list	`{ not_in: [{ var: 'status' }, ['deleted']] }`
`between`	Value between two values	`{ between: [{ var: 'age' }, 18, 65] }`
`not_between`	Value not between	`{ not_between: [{ var: 'price' }, 100, 200] }`

Array Column Operators (PostgreSQL)

For fields with type: 'array':

Operator	Description	Example
`contains`	Array contains values	`{ contains: [{ var: 'tags' }, ['urgent', 'bug']] }`
`contained_by`	Array contained by values	`{ contained_by: [{ var: 'tags' }, ['all', 'possible', 'tags']] }`
`overlaps`	Arrays overlap	`{ overlaps: [{ var: 'categories' }, ['tech', 'news']] }`
`any_of`	Value equals any element	`{ any_of: [{ var: 'tags' }, 'urgent'] }`
`not_any_of`	Value not in array	`{ not_any_of: [{ var: 'tags' }, 'spam'] }`

JSONB Operators (PostgreSQL)

Operator	Description	Example
`json_contains`	JSONB contains	`{ json_contains: [{ var: 'metadata' }, {"key": "value"}] }`
`json_has_key`	JSONB has key	`{ json_has_key: [{ var: 'metadata' }, 'priority'] }`
`json_has_any_keys`	JSONB has any keys	`{ json_has_any_keys: [{ var: 'metadata' }, ['key1', 'key2']] }`

Null Check Operators

Operator	Description	Example
`is_null`	Value is NULL	`{ is_null: [{ var: 'deletedAt' }] }`
`is_not_null`	Value is not NULL	`{ is_not_null: [{ var: 'email' }] }`

Database Dialect Support

Dialect	Key	Placeholder Style	Notes
PostgreSQL	`postgresql`	`$1`, `$2`, `$3`, ...	Full feature support including ILIKE, array operators, JSONB
MySQL	`mysql`	`?`, `?`, `?`, ...	Standard SQL features
SQL Server	`mssql`	`@p1`, `@p2`, `@p3`, ...	Standard SQL features
SQLite	`sqlite`	`?`, `?`, `?`, ...	Standard SQL features

Advanced Features

Schema Mapping Utilities

Separate public schema (for frontend) from internal database schema:

import { applyFieldMappings, toPublicSchema } from '@nam088/json-logic-to-sql';

// Define public schema (clean, no DB details)
const publicSchema: FilterSchema = {
  fields: {
    userName: { type: 'string', operators: ['eq', 'like'] },
    userEmail: { type: 'string', operators: ['eq'] },
    tags: { type: 'array', operators: ['contains'] },
    priority: { type: 'string', operators: ['eq'] },
  },
};

// Apply internal mappings on backend
const internalSchema = applyFieldMappings(publicSchema, {
  columns: {
    userName: { table: 'users', column: 'name' },
    userEmail: { table: 'users', column: 'email' },
    tags: '_tags', // Simple column rename
  },
  jsonPaths: {
    priority: "metadata->>'priority'", // JSONB path
  },
});

// Send public schema to frontend (without internal details)
const schemaForFrontend = toPublicSchema(internalSchema);

Pagination and Sorting

import { buildPagination, buildSort } from '@nam088/json-logic-to-sql';

// Build pagination
const pagination = buildPagination(
  { page: 2, pageSize: 20 },
  100, // max page size
  1    // starting param index
);
console.log(pagination.sql);
// Output: LIMIT $1 OFFSET $2
console.log(pagination.params);
// Output: { '$1': 20, '$2': 20 }

// Build sorting
const sort = buildSort(
  [
    { field: 'createdAt', direction: 'desc' },
    { field: 'name', direction: 'asc' },
  ],
  schema
);
console.log(sort.sql);
// Output: ORDER BY "created_at" DESC, "name" ASC

SELECT Clause Generation

import { buildSelect } from '@nam088/json-logic-to-sql';

const select = buildSelect(schema, {
  fields: ['firstName', 'lastName', 'email'],
  exclude: ['password'],
});
console.log(select.sql);
// Output: "first_name" AS "firstName", "last_name" AS "lastName", "email"

Complete Query Example

const compiler = new JsonLogicCompiler({ schema, dialect: 'postgresql' });

// Compile WHERE clause
const where = compiler.compile({
  and: [
    { '==': [{ var: 'status' }, 'active'] },
    { '>': [{ var: 'age' }, 18] }
  ]
});

// Build SELECT
const select = buildSelect(schema, {
  fields: ['firstName', 'lastName', 'email', 'age']
});

// Build ORDER BY
const sort = buildSort([{ field: 'lastName', direction: 'asc' }], schema);

// Build LIMIT/OFFSET
const pagination = buildPagination({ page: 1, pageSize: 20 });

// Combine into full query
const query = `
  SELECT ${select.sql}
  FROM users
  WHERE ${where.sql}
  ${sort.sql}
  ${pagination.sql}
`;

const allParams = { ...where.params, ...pagination.params };

Error Handling

The library throws specific error types for different scenarios:

import { 
  CompilerError, 
  SchemaValidationError 
} from '@nam088/json-logic-to-sql';

try {
  const result = compiler.compile(rule);
} catch (error) {
  if (error instanceof SchemaValidationError) {
    // Schema validation failed
    console.error('Field:', error.field);
    console.error('Operator:', error.operator);
    console.error('Message:', error.message);
  } else if (error instanceof CompilerError) {
    // Compilation error (invalid rule structure, etc.)
    console.error('Compilation error:', error.message);
  }
}

Security

The library implements multiple security layers:

Parameterized Queries: All values are passed as parameters, never interpolated
Identifier Escaping: Column and table names are properly quoted
Schema Validation: Only allowed fields and operators can be used
Input Sanitization: Deep sanitization of input rules
Constraint Validation: Values are validated against defined constraints
Depth Limiting: Prevents deeply nested rules that could cause performance issues
Condition Limiting: Prevents queries with excessive conditions

TypeScript Support

Full TypeScript support with comprehensive type definitions:

import type {
  FilterSchema,
  FieldSchema,
  ComputedFieldSchema,
  CompilerConfig,
  SqlResult,
  Operator,
  FieldType,
  FieldConstraints,
  FieldTransform,
  TransformFn,
  CustomTransform,
  OptionConfig,
  OptionItem,
  LookupRegistry,
  LookupConfig,
} from '@nam088/json-logic-to-sql';

Examples

479: 480: For more comprehensive examples, check out the examples directory or the unit tests which cover all supported scenarios.

Complex Nested Query

const rule = {
  or: [
    {
      and: [
        { '==': [{ var: 'status' }, 'active'] },
        { '>': [{ var: 'age' }, 18] },
        { 'in': [{ var: 'country' }, ['US', 'CA', 'UK']] }
      ]
    },
    {
      and: [
        { '==': [{ var: 'role' }, 'admin'] },
        { 'is_not_null': [{ var: 'lastLogin' }] }
      ]
    }
  ]
};

const result = compiler.compile(rule);
// Generates: ((status = $1 AND age > $2 AND country IN ($3, $4, $5)) OR (role = $6 AND last_login IS NOT NULL))

Array Operations (PostgreSQL)

const schema: FilterSchema = {
  fields: {
    tags: {
      type: 'array',
      operators: ['contains', 'overlaps', 'any_of'],
    },
  },
};

// Check if array contains all values
const rule1 = { contains: [{ var: 'tags' }, ['urgent', 'bug']] };
// SQL: tags @> $1  (where $1 = ['urgent', 'bug'])

// Check if arrays overlap
const rule2 = { overlaps: [{ var: 'tags' }, ['tech', 'news']] };
// SQL: tags && $1

// Check if value is in array column
const rule3 = { any_of: [{ var: 'tags' }, 'urgent'] };
// SQL: $1 = ANY(tags)

JSONB Queries (PostgreSQL)

const schema: FilterSchema = {
  fields: {
    metadata: {
      type: 'jsonb',
      operators: ['json_contains', 'json_has_key'],
    },
    priority: {
      type: 'string',
      operators: ['eq', 'in'],
      jsonPath: "metadata->>'priority'",
    },
  },
};

const rule = {
  and: [
    { json_has_key: [{ var: 'metadata' }, 'priority'] },
    { '==': [{ var: 'priority' }, 'high'] }
  ]
};

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

MIT

Author

@nam088

Name		Name	Last commit message	Last commit date
Latest commit History 27 Commits
.github/workflows		.github/workflows
examples		examples
src		src
.gitignore		.gitignore
.npmignore		.npmignore
.releaserc.json		.releaserc.json
CHANGELOG.md		CHANGELOG.md
LICENSE		LICENSE
QUICK_START.md		QUICK_START.md
README.md		README.md
docker-compose.yml		docker-compose.yml
jest.config.js		jest.config.js
package-lock.json		package-lock.json
package.json		package.json
tsconfig.json		tsconfig.json

License

Nam088/json-logic-to-sql

Folders and files

Latest commit

History

Repository files navigation

@nam088/json-logic-to-sql

Features

Installation

Quick Start

1. Define your Schema

2. Initialize the Compiler

3. Compile Rules

Core Concepts

Field Schema

Computed Fields

Field Transformations

Field Constraints

Supported Operators

Comparison Operators

Logical Operators

String Operators

Array Operators

Array Column Operators (PostgreSQL)

JSONB Operators (PostgreSQL)

Null Check Operators

Database Dialect Support

Advanced Features

Schema Mapping Utilities

Pagination and Sorting

SELECT Clause Generation

Complete Query Example

Error Handling

Security

TypeScript Support

Examples

Complex Nested Query

Array Operations (PostgreSQL)

JSONB Queries (PostgreSQL)

Contributing

License

Author

About

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases 10

Packages 0

Uh oh!

Languages

Packages