Skip to content

examples.md

Latest commit

 

History

History
637 lines (501 loc) · 10.6 KB

examples.md

File metadata and controls

637 lines (501 loc) · 10.6 KB

Examples

This document provides comprehensive examples of JSON Logic expressions and their SQL output.

Data Access Operations

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

Simple Comparison

{">": [{"var": "amount"}, 1000]}
WHERE amount > 1000

Equality Comparison

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

Strict Equality

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

Inequality

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

Strict Inequality

{"!==": [{"var": "count"}, 0]}
WHERE count <> 0

Equality with NULL (IS NULL)

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

Inequality with NULL (IS NOT NULL)

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

Logical NOT (with array wrapper)

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

Logical NOT (without array wrapper)

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

Logical NOT (literal)

{"!": true}
WHERE NOT (TRUE)

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 (type-appropriate SQL)

Double Negation (Empty Array)

{"!!": [[]]}
WHERE FALSE

Double Negation (Non-Empty Array)

{"!!": [[1, 2, 3]]}
WHERE TRUE

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": [
  {">": [{"var": "age"}, 18]},
  "adult",
  "minor"
]}
WHERE CASE WHEN age > 18 THEN 'adult' ELSE 'minor' END

Numeric Operations

Greater Than

{">": [{"var": "amount"}, 1000]}
WHERE amount > 1000

Greater Than or Equal

{">=": [{"var": "score"}, 80]}
WHERE score >= 80

Less Than

{"<": [{"var": "age"}, 65]}
WHERE age < 65

Less Than or Equal

{"<=": [{"var": "count"}, 10]}
WHERE count <= 10

Maximum Value

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

Minimum Value

{"min": [{"var": "price1"}, {"var": "price2"}]}
WHERE LEAST(price1, price2)

Addition

{"+": [{"var": "price"}, {"var": "tax"}]}
WHERE (price + tax)

Subtraction

{"-": [{"var": "total"}, {"var": "discount"}]}
WHERE (total - discount)

Multiplication

{"*": [{"var": "price"}, 1.2]}
WHERE (price * 1.2)

Division

{"/": [{"var": "total"}, 2]}
WHERE (total / 2)

Modulo

{"%": [{"var": "count"}, 3]}
WHERE (count % 3)

Unary Minus (Negation)

{"-": [{"var": "value"}]}
WHERE -value

Unary Plus (Cast to Number)

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

String Operands (Numeric Coercion)

String literals in arithmetic are coerced to numbers when valid, or safely quoted otherwise:

{"+": ["42", 1]}
{"*": [" 3 ", 2]}
{"+": ["3.14", 1]}
{"+": ["hello", 1]}
WHERE (42 + 1)
WHERE (3 * 2)
WHERE (3.14 + 1)
WHERE ('hello' + 1)

Large integers are preserved without precision loss:

{"+": ["9223372036854775808", 1]}
WHERE (9223372036854775808 + 1)

Array Operations

In Array

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

In Array with Type Coercion (Schema)

When a schema is provided, array elements are coerced to match the field type:

// Schema: sector_code is string type, amount is integer type
{"in": [{"var": "sector_code"}, [5960, 9000]]}
{"in": [{"var": "amount"}, ["100", "200", "300"]]}
WHERE sector_code IN ('5960', '9000')
WHERE amount IN (100, 200, 300)

String Containment

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

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 (SUM pattern)

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

All Elements Satisfy Condition

{"all": [{"var": "ages"}, {">=": [{"var": ""}, 18]}]}
WHERE NOT EXISTS (SELECT 1 FROM UNNEST(ages) AS elem WHERE NOT (elem >= 18))

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

Concatenate Strings

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

Concatenate with Conditional (Nested If)

{"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)

Complex Nested Examples

Complex Nested Math Expressions

{">": [{"+": [{"var": "base"}, {"*": [{"var": "bonus"}, 0.1]}]}, 1000]}
WHERE (base + (bonus * 0.1)) > 1000

Nested Conditions

{"and": [
  {">": [{"var": "transaction.amount"}, 10000]},
  {"or": [
    {"==": [{"var": "user.verified"}, false]},
    {"<": [{"var": "user.accountAgeDays"}, 7]}
  ]}
]}
WHERE (transaction.amount > 10000 AND (user.verified = FALSE OR user.accountAgeDays < 7))

Complex Conditional Logic

{"if": [
  {"and": [
    {">=": [{"var": "age"}, 18]},
    {"==": [{"var": "country"}, "US"]}
  ]},
  "eligible",
  "ineligible"
]}
WHERE CASE WHEN (age >= 18 AND country = 'US') THEN 'eligible' ELSE 'ineligible' END

Fraud Detection Example

{"and": [
  {">": [{"var": "transaction.amount"}, 10000]},
  {"or": [
    {"==": [{"var": "user.verified"}, false]},
    {"<": [{"var": "user.accountAgeDays"}, 7]},
    {">=": [{"var": "user.failedAttempts"}, 3]}
  ]},
  {"in": [{"var": "user.country"}, ["high_risk_1", "high_risk_2"]]}
]}
WHERE (transaction.amount > 10000 AND (user.verified = FALSE OR user.accountAgeDays < 7 OR user.failedAttempts >= 3) AND user.country IN ('high_risk_1', 'high_risk_2'))

Eligibility Check Example

{"if": [
  {"and": [
    {">=": [{"var": "age"}, 18]},
    {"==": [{"var": "hasLicense"}, true]},
    {"<": [{"var": "violations"}, 3]}
  ]},
  "approved",
  {"if": [
    {"<": [{"var": "age"}, 18]},
    "too_young",
    "rejected"
  ]}
]}
WHERE CASE WHEN (age >= 18 AND hasLicense = TRUE AND violations < 3) THEN 'approved' ELSE CASE WHEN age < 18 THEN 'too_young' ELSE 'rejected' END END

Parameterized Query Examples

All examples above can be generated with bind parameter placeholders instead of inlined literals by using the TranspileParameterized family of methods.

Simple Parameterized Comparison

sql, params, _ := jsonlogic2sql.TranspileParameterized(
    jsonlogic2sql.DialectBigQuery,
    `{"==": [{"var": "status"}, "active"]}`,
)
// sql    = "WHERE status = @p1"
// params = [{Name: "p1", Value: "active"}]

Parameterized IN List

sql, params, _ := jsonlogic2sql.TranspileParameterized(
    jsonlogic2sql.DialectBigQuery,
    `{"in": [{"var": "country"}, ["US", "CA", "MX"]]}`,
)
// sql    = "WHERE country IN (@p1, @p2, @p3)"
// params = [{Name: "p1", Value: "US"}, {Name: "p2", Value: "CA"}, {Name: "p3", Value: "MX"}]

Parameterized Nested Conditions (PostgreSQL)

sql, params, _ := jsonlogic2sql.TranspileParameterized(
    jsonlogic2sql.DialectPostgreSQL,
    `{"and": [{">": [{"var": "amount"}, 10000]}, {"==": [{"var": "status"}, "pending"]}]}`,
)
// sql    = "WHERE (amount > $1 AND status = $2)"
// params = [{Name: "p1", Value: 10000}, {Name: "p2", Value: "pending"}]

Parameterized Condition (Without WHERE)

condition, params, _ := jsonlogic2sql.TranspileConditionParameterized(
    jsonlogic2sql.DialectBigQuery,
    `{">": [{"var": "amount"}, 1000]}`,
)
// condition = "amount > @p1"
// params    = [{Name: "p1", Value: 1000}]

query := fmt.Sprintf("SELECT * FROM orders WHERE %s AND created_at > @date", condition)

See Parameterized Queries for full documentation.

See Also