Operator expressions

-- Without parentheses (precedence applies)
SELECT * FROM products WHERE price > 100 AND category = 'electronics' OR featured = true
-- Equivalent to: (price > 100 AND category = 'electronics') OR featured = true

-- With parentheses (overriding precedence)
SELECT * FROM products WHERE price > 100 AND (category = 'electronics' OR featured = true)
-- Different result: price > 100 AND (category matches either condition)

-- Arithmetic precedence
SELECT price * 1.1 + 5 FROM products
-- Equivalent to: (price * 1.1) + 5 (multiplication before addition)

SELECT * FROM your_collection_name WHERE field1 = [0, 1]

SELECT * FROM your_collection_name WHERE field1 = :your_array

ARRAY value_expr FOR [name_var:]value_var IN|WITHIN source [WHEN condition] END

-- Filter even numbers and transform to objects
ARRAY {"index":i,"val":v} FOR i:v IN [1,2,3] WHEN v%2 = 0 END
-- Result: [{"index":1,"val":2}]

-- Convert array elements to strings, excluding elements with a value of 1
ARRAY cast(v,'string') FOR v IN [1,2,3] WHEN v != 1 END
-- Result: ["2","3"]

-- Transform string characters based on position
ARRAY CASE WHEN i%2 = 0 THEN "foo" ELSE "bar" END FOR i:v IN split("hello","") END
-- Result: ["foo","bar","foo","bar","foo"]

-- Extract values from an object
ARRAY v FOR n:v IN {"a":"one","b":"two"} END
-- Result: ["one","two"]

-- Recursively flatten nested arrays
ARRAY v FOR v WITHIN [[1,2],3] END
-- Result: [[1,2],1,2,3]

-- Use in SELECT to transform document fields
SELECT ARRAY price * 0.9 FOR price IN prices END AS discounted_prices
FROM products

OBJECT name_expr:value_expr FOR [name_var:]value_var IN|WITHIN source [WHEN condition] END

-- Transform object keys to uppercase, filter by value length
OBJECT upper(n):v FOR n:v IN {"a":"one","b":"two","c":"three"} WHEN len(v) = 3 END
-- Result: {"A":"one","B":"two"}

-- Convert array to object with generated field names
OBJECT "field_"||cast(i,"string"):v FOR i:v IN [1,2,3] END
-- Result: {"field_0":1,"field_1":2,"field_2":3}

-- Filter object by value type
OBJECT n:v FOR n:v IN {"a":1,"b":[],"c":2} WHEN type(v) != 'array' END
-- Result: {"a":1,"c":2}

-- Process nested object (WITHIN flattens structure)
OBJECT n:v FOR n:v WITHIN {"a":{"b":1}} END
-- Result: {"a":{"b":1},"b":1}

-- Use in SELECT to filter object fields (alternative to object_unset)
SELECT OBJECT n:v FOR n:v IN u WHEN n NOT IN ("salary","bonus") END
FROM users u

-- Transform array of prices with tax calculation
SELECT ARRAY price * 1.08 FOR price IN item_prices END AS prices_with_tax
FROM orders

-- Extract active items only
SELECT ARRAY item FOR item IN items WHEN item.status = 'active' END AS active_items
FROM inventory

-- Convert array to lookup object
SELECT OBJECT item._id:item.name FOR item IN catalog END AS id_to_name_map
FROM product_catalog

-- Flatten nested tag arrays
SELECT ARRAY tag FOR tag WITHIN categories END AS all_tags
FROM articles

ANY [name_var:]value_var IN|WITHIN expression SATISFIES condition END
EVERY [name_var:]value_var IN|WITHIN expression SATISFIES condition END
ANY AND EVERY [name_var:]value_var IN|WITHIN expression SATISFIES condition END

-- Find documents where any array element is less than zero after the first position
SELECT * FROM test
WHERE ANY n:v IN test.array_field SATISFIES n > 0 AND v < 0 END

-- Find documents where any nested field is NULL
SELECT * FROM test
WHERE ANY v WITHIN test.details SATISFIES v IS NULL END

-- Find orders where any item was modified after the order date
SELECT * FROM orders o
WHERE ANY v IN o.items SATISFIES v.modified > o.order_date END

-- Find products with any tag matching "electronics"
SELECT * FROM products
WHERE ANY tag IN tags SATISFIES tag = 'electronics' END

-- Find documents where all prices are above 100
SELECT * FROM products
WHERE EVERY price IN prices SATISFIES price > 100 END

-- Verify all items in an order are available
SELECT * FROM orders
WHERE EVERY item IN items SATISFIES item.status = 'available' END

-- Check that all nested values are positive
SELECT * FROM data
WHERE EVERY v WITHIN measurements SATISFIES v > 0 END

-- Find documents with at least one item, and all items are approved
-- (Empty items array would fail this check)
SELECT * FROM orders
WHERE ANY AND EVERY item IN items SATISFIES item.approved = true END

-- Ensure array is non-empty and all values are within range
SELECT * FROM data
WHERE ANY AND EVERY v IN values SATISFIES v BETWEEN 0 AND 100 END

-- Searches only the immediate array elements
ANY v IN [1, 2, [3, 4]] SATISFIES v = 3 END
-- Result: false (3 is nested inside [3,4])

-- Searches recursively through all nested levels
ANY v WITHIN [1, 2, [3, 4]] SATISFIES v = 3 END
-- Result: true (finds 3 in nested array)

-- With objects
ANY v WITHIN {"a": {"b": 1}} SATISFIES v = 1 END
-- Result: true (recursively finds 1 in nested object)

SELECT * FROM products
WHERE ANY category IN categories SATISFIES category LIKE 'electronics%' END

SELECT * FROM shipments
WHERE EVERY package IN packages SATISFIES package.weight < 50 END

SELECT * FROM orders
WHERE ANY AND EVERY item IN items SATISFIES item.quantity > 0 END

SELECT * FROM documents
WHERE ANY v WITHIN metadata SATISFIES v = 'confidential' END

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  [ELSE default_result]
END

SELECT
  color,
  CASE color
    WHEN 'blue' THEN 'ocean'
    WHEN 'red' THEN 'fire'
    WHEN 'green' THEN 'forest'
    ELSE 'unknown'
  END AS theme
FROM cars

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  [ELSE default_result]
END

SELECT
  year,
  mileage,
  CASE
    WHEN year > 2020 AND mileage < 20000 THEN 'new'
    WHEN year > 2015 AND mileage < 80000 THEN 'good'
    WHEN year > 2010 THEN 'fair'
    ELSE 'old'
  END AS condition
FROM cars