Notation for SQL conditions in JSON strings

Compact String Notation for Generating SQL Conditions from JSON

When building SQL queries from JSON files, you often end up with lengthy conditional expressions. The original approach used arrays like "vendor_id": [17,-23], which converted to vendor_id = 17 OR NOT vendor_id = 23. It's concise but limited—no support for ranges, MIN/MAX aggregations, and ambiguous syntax.

The new notation solves this with a string format validated by regular expressions. It covers comparisons, sets, ranges, and negations for any data types using =, <, >, ! operators. The minus sign denotes logical negation, regardless of data type.

Core Notation Constructs

The notation converts strings like X=operand into standard SQL. It supports binary functions like MIN, MAX, and BETWEEN.

X=y        -> X = y
X<y        -> X < y
X>y        -> X > y

X!=y       -> X < y OR X > y
X<=y       -> X < y OR X = y
X>=y       -> X > y OR X = y

For two-element sets:

X=[y1, y2]     -> (X = y1 OR X = y2)
X>[y1, y2]     -> (X > MAX(y1, y2))
X<[y1, y2]     -> (X < MIN(y1, y2))

X!=[y1, y2]    -> (X != y1 AND X != y2)
X<=[y1, y2]    -> (X <= MIN(y1, y2))
X>=[y1, y2]    -> (X >= MAX(y1, y2))

X>>[y1, y2]    -> BETWEEN(y1, y2)
X><[y1, y2]    -> (NOT BETWEEN(y1, y2))

For sets of any size, the logic is similar: OR for equalities, AND for negations, MIN/MAX for boundaries.

Advantages Over Arrays

Switching from JSON arrays to strings simplifies parsing and validation:

• Validation: Regular expressions instead of per-element schema checks.
• Completeness: Built-in support for ranges and aggregations—no extensions needed.
• Readability: Leverages mathematical intuition—X>[y1,y2] means X is greater than all elements.

Example of shortening:

Traditional SQL: WHERE (age >= 18 AND age <= 65) OR age > 70

Notation: age>=[18,65] OR age>70

AI Model Evaluation

DeepSeek analysis highlights the strengths:

• Compactness: High information density without losing meaning.
• Intuitiveness: X!=[y1,y2] clearly means not equal to any.
• Extensibility: Easy to add ranges with >> (inclusive) and >< (exclusive).

Potential issues:

• Ambiguity for beginners: X>[5,10] requires understanding MAX.
• Empty sets: Need to define behavior for X=[].
• Conflicts: >> might clash with bitwise shifts.

Improvement suggestions:

• Range alternatives: X..[y1,y2], X:[y1,y2].
• Parentheses for strict ranges: X>>(10,20).

Analogues and Uniqueness

Closest analogues:

| Language/DSL | Example | Similarity |

|--------------|--------------------------|-----------------------------|

| Quist | 300 <= number < 500 | Double comparisons, sets |

| SQL++ | lang IN ["en", "de"]| IN for arrays |

| PRQL | derive full_name = ... | Functional approach |

Notation uniqueness:

• Arrows >>, >< for BETWEEN/NOT BETWEEN.
• Combinations like >=[y1,y2] for set boundaries.
• Minimalism with full expressiveness.

Comparison:

• Notation: age>=[18,65] OR status=[active,pending]
• Quist: (18 <= age <= 65 OR status:in active, pending)
• SQL: (age >= 18 AND age <= 65) OR status IN ('active', 'pending')

Key Takeaways

• Designed for SQL preprocessors or JSON DSLs in backends.
• Regex-based parsing: fast, no complex parsers needed.
• Supports any comparable data types.
• Logical minus for negations simplifies unification.
• First version for discussion—no optimizations yet.

— Editorial Team