Skip to content

operators.md

Latest commit

 

History

History
435 lines (342 loc) · 9.11 KB

operators.md

File metadata and controls

435 lines (342 loc) · 9.11 KB

Supported Operators

This document lists all JSON Logic operators supported by jsonlogic2sql.

Data Access

Operator Description Example
var Access variable values (including array indexing) {"var": "name"}
missing Check if variable(s) are missing {"missing": "email"}
missing_some Check if some variables are missing {"missing_some": [1, ["a", "b"]]}

Variable Access

{"var": "name"}
WHERE name

Variable with Array Index

{"var": 1}
WHERE data[1]

Variable with Default Value

{"var": ["status", "pending"]}
WHERE COALESCE(status, 'pending')

Missing Field Check (Single)

{"missing": "email"}
WHERE email IS NULL

Missing Field Check (Multiple)

{"missing": ["email", "phone"]}
WHERE (email IS NULL OR phone IS NULL)

Missing Some Fields

{"missing_some": [1, ["field1", "field2"]]}
WHERE (field1 IS NULL OR field2 IS NULL)

Logic and Boolean Operations

Operator Description
if Conditional expressions
==, === Equality comparison
!=, !== Inequality comparison
! Logical NOT
!! Double negation (boolean conversion)
or Logical OR
and Logical AND

Equality Comparison

{"==": [{"var": "status"}, "active"]}
WHERE status = 'active'

Strict Equality

{"===": [{"var": "count"}, 5]}
WHERE count = 5

Inequality

{"!=": [{"var": "status"}, "inactive"]}
WHERE status != 'inactive'

Equality with NULL

{"==": [{"var": "deleted_at"}, null]}
WHERE deleted_at IS NULL

Inequality with NULL

{"!=": [{"var": "field"}, null]}
WHERE field IS NOT NULL

Logical NOT

{"!": [{"var": "isDeleted"}]}
WHERE NOT (isDeleted)

Double Negation (Boolean Conversion)

{"!!": [{"var": "value"}]}

Without schema (generic truthiness check):

WHERE (value IS NOT NULL AND value != FALSE AND value != 0 AND value != '')

With schema, the !! operator generates type-appropriate SQL. See Schema-Aware Truthiness for details.

Logical AND

{"and": [
  {">": [{"var": "amount"}, 5000]},
  {"==": [{"var": "status"}, "pending"]}
]}
WHERE (amount > 5000 AND status = 'pending')

Logical OR

{"or": [
  {">=": [{"var": "failedAttempts"}, 5]},
  {"in": [{"var": "country"}, ["CN", "RU"]]}
]}
WHERE (failedAttempts >= 5 OR country IN ('CN', 'RU'))

Conditional Expression (if)

{"if": [
  {">": [{"var": "age"}, 18]},
  "adult",
  "minor"
]}
WHERE CASE WHEN age > 18 THEN 'adult' ELSE 'minor' END

Numeric Operations

Operator Description
>, >=, <, <= Comparison operators
max, min Maximum/minimum values
+, -, *, /, % Arithmetic operations

Comparison Operators

{">": [{"var": "amount"}, 1000]}
{">=": [{"var": "score"}, 80]}
{"<": [{"var": "age"}, 65]}
{"<=": [{"var": "count"}, 10]}
WHERE amount > 1000
WHERE score >= 80
WHERE age < 65
WHERE count <= 10

Maximum/Minimum

{"max": [{"var": "score1"}, {"var": "score2"}, {"var": "score3"}]}
{"min": [{"var": "price1"}, {"var": "price2"}]}
WHERE GREATEST(score1, score2, score3)
WHERE LEAST(price1, price2)

Arithmetic Operations

{"+": [{"var": "price"}, {"var": "tax"}]}
{"-": [{"var": "total"}, {"var": "discount"}]}
{"*": [{"var": "price"}, 1.2]}
{"/": [{"var": "total"}, 2]}
{"%": [{"var": "count"}, 3]}
WHERE (price + tax)
WHERE (total - discount)
WHERE (price * 1.2)
WHERE (total / 2)
WHERE (count % 3)

String Operands in Arithmetic

When string literals appear in numeric operations, the transpiler coerces them following JSONLogic's JavaScript-like semantics:

  • Valid numeric strings are coerced to numbers: "42" becomes 42, "3.14" becomes 3.14
  • Whitespace-padded numeric strings are trimmed then coerced: " 3 " becomes 3
  • Non-numeric strings are safely quoted as string literals: "hello" becomes 'hello'
  • Special float values ("NaN", "Inf") are safely quoted: "NaN" becomes 'NaN'
  • Large integers are preserved exactly without float64 precision loss: "9223372036854775808" stays 9223372036854775808
{"+": ["42", 1]}
{"*": [" 3 ", 2]}
{"+": ["hello", 1]}
WHERE (42 + 1)
WHERE (3 * 2)
WHERE ('hello' + 1)

Unary Operations

{"-": [{"var": "value"}]}
{"+": ["-5"]}
WHERE -value
WHERE CAST(-5 AS NUMERIC)

Array Operations

Operator Description
in Check if value is in array
map, filter, reduce Array transformations
all, some, none Array condition checks
merge Merge arrays

In Array

{"in": [{"var": "country"}, ["US", "CA", "MX"]]}
WHERE country IN ('US', 'CA', 'MX')

When the right-hand side is an array-typed field (with schema), in uses dialect-specific array membership syntax (e.g., BigQuery/Spanner use value IN UNNEST(array); PostgreSQL uses value = ANY(array)).

When a schema is provided, array elements are automatically coerced to match the field type. For example, numeric values in the array are quoted as strings when the field is a string type:

// Schema: merchant_code is string type
{"in": [{"var": "merchant_code"}, [5960, 9000]]}
WHERE merchant_code IN ('5960', '9000')

See Type Coercion for details.

Map Array

{"map": [{"var": "numbers"}, {"+": [{"var": "item"}, 1]}]}
WHERE ARRAY(SELECT (elem + 1) FROM UNNEST(numbers) AS elem)

Filter Array

{"filter": [{"var": "scores"}, {">": [{"var": "item"}, 70]}]}
WHERE ARRAY(SELECT elem FROM UNNEST(scores) AS elem WHERE elem > 70)

Reduce Array

{"reduce": [{"var": "numbers"}, {"+": [{"var": "accumulator"}, {"var": "current"}]}, 0]}
WHERE 0 + COALESCE((SELECT SUM(elem) FROM UNNEST(numbers) AS elem), 0)

Nested Array Scope

For nested array operators, the transpiler keeps inner and outer element scopes distinct (for example elem, elem1) when needed, so references like item.base in nested reducers resolve to the intended outer element.

Inside an inner lambda, current and current.* always refer to that inner element alias (for example elem1, elem1.base). Outer-element access in nested lambdas should use item.*.

All Elements Satisfy Condition

{"all": [{"var": "ages"}, {">=": [{"var": ""}, 18]}]}
-- BigQuery/Spanner
WHERE (ARRAY_LENGTH(ages) > 0 AND NOT EXISTS (SELECT 1 FROM UNNEST(ages) AS elem WHERE NOT (elem >= 18)))
-- PostgreSQL
WHERE (CARDINALITY(ages) > 0 AND NOT EXISTS (SELECT 1 FROM UNNEST(ages) AS elem WHERE NOT (elem >= 18)))
-- DuckDB
WHERE (length(ages) > 0 AND NOT EXISTS (SELECT 1 FROM UNNEST(ages) AS elem WHERE NOT (elem >= 18)))
-- ClickHouse
WHERE (length(ages) > 0 AND arrayAll(elem -> elem >= 18, ages))

Note: The array length guard ensures JSONLogic spec compliance - {"all": [[], condition]} returns false (not true). Each dialect uses its native array length function: ARRAY_LENGTH (BigQuery/Spanner), CARDINALITY (PostgreSQL), length (DuckDB/ClickHouse).

Some Elements Satisfy Condition

{"some": [{"var": "statuses"}, {"==": [{"var": ""}, "active"]}]}
WHERE EXISTS (SELECT 1 FROM UNNEST(statuses) AS elem WHERE elem = 'active')

No Elements Satisfy Condition

{"none": [{"var": "values"}, {"==": [{"var": ""}, "invalid"]}]}
WHERE NOT EXISTS (SELECT 1 FROM UNNEST(values) AS elem WHERE elem = 'invalid')

Merge Arrays

{"merge": [{"var": "array1"}, {"var": "array2"}]}
WHERE ARRAY_CONCAT(array1, array2)

String Operations

Operator Description
in Check if substring is in string
cat Concatenate strings
substr Substring operations

String Containment

{"in": ["hello", "hello world"]}
WHERE POSITION('hello' IN 'hello world') > 0

Concatenate Strings

{"cat": [{"var": "firstName"}, " ", {"var": "lastName"}]}
WHERE CONCAT(firstName, ' ', lastName)

Concatenate with Conditional

{"cat": [{"if": [{"==": [{"var": "gender"}, "M"]}, "Mr. ", "Ms. "]}, {"var": "first_name"}, " ", {"var": "last_name"}]}
WHERE CONCAT(CASE WHEN (gender = 'M') THEN 'Mr. ' ELSE 'Ms. ' END, first_name, ' ', last_name)

Substring with Length

{"substr": [{"var": "email"}, 0, 10]}
WHERE SUBSTR(email, 1, 10)

Substring without Length

{"substr": [{"var": "email"}, 4]}
WHERE SUBSTR(email, 5)

See Also