This reference documents the complete set of Chalk SQL functions for federated queries across data sources, offline stores, and the Chalk catalog.
SELECT pow(2.0, 8.0);
| pow(2.0, 8.0) |
| ------------- |
| 256.0 |value is between two bounds (inclusive).Type parameters. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
value. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.condition is true, otherwise returns the third argument.Type parameters. T means matching inputs and outputs use the same type.
T means matching inputs and outputs use the same type.T means matching inputs and outputs use the same type.array are true.SELECT array_all(transform(ARRAY['alice@example.com', 'bob@example.com', 'carol-at-example.com'], e -> strpos(e, '@') > 0)) AS all_emails_valid;
| all_emails_valid |
| ---------------- |
| false |SELECT array_all(ARRAY[true, NULL, true]) AS all_true_ignoring_nulls;
| all_true_ignoring_nulls |
| ----------------------- |
| true |array is true.SELECT array_any(transform(ARRAY['crypto', 'wire', 'gift card'], k -> strpos(lower('Urgent WIRE transfer to new payee'), k) > 0)) AS memo_is_risky;
| memo_is_risky |
| ------------- |
| true |SELECT array_any(ARRAY[false, CAST(NULL AS BOOLEAN), false]) AS any_rule_fired;
| any_rule_fired |
| -------------- |
| false |element to the end of an array.Type parameters. T means matching inputs and outputs use the same type.
SELECT array_append(ARRAY['login', 'view_cart'], 'checkout') AS session_events;
| session_events |
| ---------------------------- |
| [login, view_cart, checkout] |SELECT array_max(array_append(ARRAY[42.50, 18.99, 120.00], 310.75)) AS max_txn_amount_incl_current;
| max_txn_amount_incl_current |
| --------------------------- |
| 310.75 |SELECT array_apply(ARRAY[' Groceries', 'FUEL ', 'Dining'], tag -> lower(trim(tag))) AS normalized_tags;
| normalized_tags |
| ------------------------- |
| [groceries, fuel, dining] |SELECT array_apply(ARRAY[19.99, 5.0, 120.5], x -> round(x * 1.08, 2)) AS prices_with_tax;
| prices_with_tax |
| -------------------- |
| [21.59, 5.4, 130.14] |array.SELECT element_at(ARRAY['groceries', 'travel', 'dining', 'utilities'], array_arg_max(ARRAY[0.12, 0.71, 0.05, 0.12]) + 1) AS predicted_category;
| predicted_category |
| ------------------ |
| travel |SELECT array_arg_max(ARRAY[42, 87, NULL, 87, 15]) AS busiest_hour_index;
| busiest_hour_index |
| ------------------ |
| 1 |array.SELECT element_at(ARRAY['ups', 'fedex', 'usps', 'dhl'], array_arg_min(ARRAY[412.50, 389.99, 405.00, 395.25]) + 1) AS cheapest_carrier;
| cheapest_carrier |
| ---------------- |
| fedex |SELECT array_arg_min(ARRAY[CAST(NULL AS DOUBLE), 0.42, 0.17, 0.88, 0.17]) AS nearest_centroid_idx;
| nearest_centroid_idx |
| -------------------- |
| 2 |array.SELECT round(array_average(ARRAY[42.50, 18.99, 120.00, 7.25, 61.30]), 2) AS avg_recent_txn_amount;
| avg_recent_txn_amount |
| --------------------- |
| 50.01 |SELECT array_average(ARRAY[4.5, NULL, 5.0, 2.5]) AS avg_review_rating;
| avg_review_rating |
| ----------------- |
| 4.0 |array.Type parameters. T means matching inputs and outputs use the same type.
SELECT array_compact(ARRAY[CASE WHEN 1250.00 > 1000 THEN 'large_amount' END, CASE WHEN 'US' <> 'BR' THEN 'cross_border' END, CASE WHEN 3 > 5 THEN 'high_velocity' END]) AS triggered_rules;
| triggered_rules |
| ---------------------------- |
| [large_amount, cross_border] |SELECT cardinality(array_compact(ARRAY['ada@example.com', NULL, '+1-555-0100', NULL])) AS contact_methods_on_file;
| contact_methods_on_file |
| ----------------------- |
| 2 |SELECT array_contains(split(lower('Refund for order 8812 - duplicate charge'), ' '), 'refund') AS memo_mentions_refund;
| memo_mentions_refund |
| -------------------- |
| true |SELECT array_contains(ARRAY[5411, 5812, 7995], 7995) AS has_gambling_mcc;
| has_gambling_mcc |
| ---------------- |
| true |array contains the given element.SELECT array_contains_element(split(lower('Wire transfer to Crypto exchange'), ' '), 'crypto') AS memo_mentions_crypto;
| memo_mentions_crypto |
| -------------------- |
| true |SELECT array_contains_element(ARRAY[5411, 5812, 5541], 7995) AS used_gambling_mcc;
| used_gambling_mcc |
| ----------------- |
| false |array.SELECT array_cum_sum(ARRAY[500.00, -120.50, -45.25, 250.00, -80.00]) AS running_balance;
| running_balance |
| -------------------------------------- |
| [500.0, 379.5, 334.25, 584.25, 504.25] |SELECT array_cum_sum(transform(ARRAY[3, 5, NULL, 2], x -> coalesce(x, 0))) AS running_total;
| running_total |
| ------------- |
| [3, 8, 8, 10] |array with duplicate elements removed, preserving order.Type parameters. T means matching inputs and outputs use the same type.
SELECT array_distinct(ARRAY['card_present', 'online', 'card_present', 'atm', 'online', 'online']) AS distinct_channels;
| distinct_channels |
| --------------------------- |
| [card_present, online, atm] |SELECT cardinality(array_distinct(ARRAY['dev_a1', 'dev_b7', 'dev_a1', 'dev_c3', 'dev_b7'])) AS distinct_device_count;
| distinct_device_count |
| --------------------- |
| 3 |array containing only the duplicate elements from the input array.SELECT array_duplicates(split('INV-1042,INV-2210,INV-1042,INV-3301,INV-2210,INV-1042', ',')) AS repeated_invoice_ids;
| repeated_invoice_ids |
| -------------------- |
| [INV-1042, INV-2210] |SELECT cardinality(array_duplicates(ARRAY[4417, 9012, 4417, 3388, 9012, 4417])) AS reused_device_count;
| reused_device_count |
| ------------------- |
| 2 |Type parameters. T means matching inputs and outputs use the same type.
SELECT array_except(ARRAY['electronics', 'books', 'garden', 'toys'], ARRAY['books', 'toys']) AS unexplored_categories;
| unexplored_categories |
| --------------------- |
| [electronics, garden] |SELECT array_except(ARRAY[412, 205, 412, 309, 205, 118], ARRAY[309, 777]) AS new_merchant_ids;
| new_merchant_ids |
| ---------------- |
| [412, 205, 118] |array using a callback function predicate.Type parameters. T means matching inputs and outputs use the same type.
array or list. T means matching inputs and outputs use the same type.SELECT array_filter(ARRAY[12.50, -3.00, 249.99, -45.00, 8.75], x -> x < 0) AS refund_amounts;
| refund_amounts |
| -------------- |
| [-3.0, -45.0] |SELECT array_filter(split('urgent,billing,,refund,priority:high', ','), t -> t <> '' AND NOT starts_with(t, 'priority:')) AS ticket_tags;
| ticket_tags |
| ------------------------- |
| [urgent, billing, refund] |array.Type parameters. T means matching inputs and outputs use the same type.
array or list. T means matching inputs and outputs use the same type.SELECT array_frequency(ARRAY['card_present','online','online','atm','online']) AS channel_counts;
| channel_counts |
| ------------------------------------ |
| {card_present: 1, online: 3, atm: 1} |SELECT element_at(array_frequency(split(lower('Refund please. I asked for a refund twice and still no refund'), ' ')), 'refund') AS refund_mentions;
| refund_mentions |
| --------------- |
| 3 |array contains duplicate elements.Type parameters. T means matching inputs and outputs use the same type.
SELECT array_has_duplicates(ARRAY['dev_a1', 'dev_b7', 'dev_a1', 'dev_c3']) AS reused_device;
| reused_device |
| ------------- |
| true |SELECT array_has_duplicates(transform(split('Alice@Example.com,bob@example.com,alice@example.com', ','), e -> lower(e))) AS shared_email;
| shared_email |
| ------------ |
| true |SELECT array_indexof(ARRAY['approved', 'approved', 'declined', 'approved'], 'declined') AS first_decline_attempt;
| first_decline_attempt |
| --------------------- |
| 3 |SELECT array_indexof(ARRAY['low', 'medium', 'high', 'urgent'], 'critical') AS priority_rank;
| priority_rank |
| ------------- |
| 0 |Type parameters. T means matching inputs and outputs use the same type.
SELECT array_intersect(ARRAY['refund','urgent','billing','shipping'], ARRAY['billing','refund','chargeback']) AS escalation_tags;
| escalation_tags |
| ----------------- |
| [refund, billing] |SELECT cardinality(array_intersect(split(lower('Wireless Noise Cancelling Headphones Black'), ' '), split(lower('noise cancelling headphones'), ' '))) AS query_term_matches;
| query_term_matches |
| ------------------ |
| 3 |SELECT array_intersect(ARRAY[3, 1, 3, 2], ARRAY[3, 3, 2]) AS shared;
| shared |
| ------ |
| [3, 2] |array elements into a string using a separator and optional null replacement.Type parameters. K is the key type of the input map.
SELECT array_join(array_constructor('a', 'b', 'c'), ',');
| array_join(array_constructor('a', 'b', 'c'), ',') |
| ------------------------------------------------- |
| a,b,c |SELECT array_length(split('refund requested for order 4821 damaged in shipping', ' ')) AS memo_word_count;
| memo_word_count |
| --------------- |
| 8 |SELECT array_length(array_distinct(ARRAY['US','US','GB','NG','US'])) AS distinct_login_countries;
| distinct_login_countries |
| ------------------------ |
| 3 |array.Type parameters. T means matching inputs and outputs use the same type.
SELECT array_max(ARRAY[42.50, 18.99, 129.00, 7.25]) AS largest_purchase;
| largest_purchase |
| ---------------- |
| 129.0 |SELECT array_max(remove_nulls(ARRAY[0.12, NULL, 0.87, 0.45])) AS peak_fraud_score;
| peak_fraud_score |
| ---------------- |
| 0.87 |SELECT array_max(ARRAY[DATE '2026-03-14', DATE '2026-09-02', DATE '2026-07-21']) AS latest_login_date;
| latest_login_date |
| ----------------- |
| 2026-09-02 |array.SELECT array_median(ARRAY[42.50, 38.00, 2499.99, 45.25, 40.10]) AS median_txn_amount;
| median_txn_amount |
| ----------------- |
| 42.5 |SELECT array_median(ARRAY[120, 95, 310, 140]) AS median_response_ms;
| median_response_ms |
| ------------------ |
| 130.0 |SELECT array_median(remove_nulls(ARRAY[4.5, NULL, 3.0, 5.0])) AS median_rating;
| median_rating |
| ------------- |
| 4.5 |array.Type parameters. T means matching inputs and outputs use the same type.
SELECT array_min(ARRAY[42.5, 18.0, 7.25, 63.1]) AS smallest_txn_amount;
| smallest_txn_amount |
| ------------------- |
| 7.25 |SELECT array_min(ARRAY[TIMESTAMP '2024-03-14 09:12:00', TIMESTAMP '2024-01-02 18:40:00', TIMESTAMP '2024-02-27 07:05:00']) AS first_login_at;
| first_login_at |
| ----------------------- |
| 2024-01-02 18:40:00 UTC |array.SELECT array_mode(array_constructor(1, 2, 2, 3));
| array_mode(array_constructor(1, 2, 2, 3)) |
| ----------------------------------------- |
| 2 |array.SELECT array_multimode(ARRAY['grocery','fuel','grocery','dining','fuel','travel']) AS top_categories;
| top_categories |
| --------------- |
| [grocery, fuel] |SELECT cardinality(array_multimode(ARRAY['card','ach','card','wire','ach'])) > 1 AS has_tied_payment_method;
| has_tied_payment_method |
| ----------------------- |
| true |array.SELECT array_n_unique(ARRAY['US', 'US', 'MX', NULL, 'BR', 'US']) AS distinct_card_countries;
| distinct_card_countries |
| ----------------------- |
| 3 |SELECT array_n_unique(split(lower('Refund refund REFUND please'), ' ')) AS distinct_memo_words;
| distinct_memo_words |
| ------------------- |
| 2 |SELECT array_normalize(ARRAY[2.0, -1.0, 4.0, 2.0], 2.0) AS unit_embedding;
| unit_embedding |
| --------------------- |
| [0.4, -0.2, 0.8, 0.4] |SELECT array_normalize(ARRAY[12.0, 3.0, 0.0, 5.0], 1.0) AS category_spend_share;
| category_spend_share |
| ---------------------- |
| [0.6, 0.15, 0.0, 0.25] |element in an array.Type parameters. T means matching inputs and outputs use the same type.
SELECT array_position(array_constructor('a', 'b', 'a'), 'a');
| array_position(array_constructor('a', 'b', 'a'), 'a') |
| ----------------------------------------------------- |
| 1 |array and returns the accumulated value.Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type. V is the value type of the input map.
array or list. T means matching inputs and outputs use the same type.T means matching inputs and outputs use the same type. U is a second generic value type.SELECT array_reduce(ARRAY[0.02, -0.01, 0.03, 0.015], 1.0, (growth, r) -> growth * (1 + r), growth -> round((growth - 1) * 100, 2)) AS cumulative_return_pct;
| cumulative_return_pct |
| --------------------- |
| 5.57 |SELECT array_reduce(ARRAY['ok', 'fail', 'fail', 'ok', 'fail', 'fail', 'fail', 'ok'], struct_pack(cur := 0, best := 0), (s, status) -> IF(status = 'fail', struct_pack(cur := s.cur + 1, best := greatest(s.best, s.cur + 1)), struct_pack(cur := 0, best := s.best)), s -> s.best) AS longest_failure_streak;
| longest_failure_streak |
| ---------------------- |
| 3 |array with Python-style loop control. Lambda returns (new_state, control_code, result), where control_code is 0=normal, 1=return, 2=continue, 3=break. Returns row(final_state, control_code, result|null).Type parameters. T means matching inputs and outputs use the same type. S is the callback state type. R is the callback result type.
array or list. T means matching inputs and outputs use the same type.function applied to the elements. T means matching inputs and outputs use the same type. R is the callback result type. S is the callback state type.element from an array.Type parameters. T means matching inputs and outputs use the same type.
SELECT array_remove(split('POS PURCHASE ACME COFFEE', ' '), '') AS memo_tokens;
| memo_tokens |
| ----------------------------- |
| [POS, PURCHASE, ACME, COFFEE] |SELECT cardinality(array_remove(ARRAY[42, -1, 17, -1, 8], -1)) AS valid_reading_count;
| valid_reading_count |
| ------------------- |
| 3 |array with elements in reverse order.SELECT array_reverse(ARRAY['login', 'view_cart', 'add_payment', 'checkout']) AS events_newest_first;
| events_newest_first |
| ----------------------------------------- |
| [checkout, add_payment, view_cart, login] |SELECT element_at(array_reverse(split('electronics/computers/laptops/gaming', '/')), 1) AS leaf_category;
| leaf_category |
| ------------- |
| gaming |Type parameters. E is the element type of the input array or list.
SELECT array_slice(array_constructor(1, 2, 3, 4), 2, 3);
| array_slice(array_constructor(1, 2, 3, 4), 2, 3) |
| ------------------------------------------------ |
| [2, 3, 4] |array in ascending order, optionally using a key extraction function.Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.
SELECT array_sort(array_constructor(3, 1, 2));
| array_sort(array_constructor(3, 1, 2)) |
| -------------------------------------- |
| [1, 2, 3] |array in descending order, optionally using a key extraction function.Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.
SELECT array_sort_desc(array_constructor(3, 1, 2));
| array_sort_desc(array_constructor(3, 1, 2)) |
| ------------------------------------------- |
| [3, 2, 1] |array.SELECT round(array_stddev(ARRAY[42.50, 38.75, 45.10, 40.00, 512.99], false), 2) AS txn_amount_stddev;
| txn_amount_stddev |
| ----------------- |
| 188.57 |SELECT round(array_stddev(ARRAY[1200.0, 1350.0, 1180.0, 1420.0], true), 2) AS daily_spend_sample_stddev;
| daily_spend_sample_stddev |
| ------------------------- |
| 116.44 |array.SELECT array_sum(ARRAY[3, NULL, 0, 5, NULL, 2, 4]) AS logins_last_7_days;
| logins_last_7_days |
| ------------------ |
| 14 |SELECT array_sum(transform(ARRAY[2, 1, 3], qty -> qty * 19.99)) AS order_total;
| order_total |
| ----------- |
| 119.94 |SELECT array_to_string(array_sort(ARRAY['travel', 'electronics', 'grocery']), '|') AS category_key;
| category_key |
| ---------------------------- |
| electronics\|grocery\|travel |SELECT array_to_string(ARRAY['US', NULL, 'CA'], ',') AS ship_countries;
| ship_countries |
| -------------- |
| US,CA |function to each element in an array and returns a new array.Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.
SELECT array_transform(ARRAY[' Groceries', 'DINING ', 'Travel'], tag -> lower(trim(tag))) AS normalized_tags;
| normalized_tags |
| --------------------------- |
| [groceries, dining, travel] |SELECT array_transform(ARRAY[1200, 45, 8800], amount_cents -> amount_cents >= 5000) AS is_large_txn;
| is_large_txn |
| -------------------- |
| [false, false, true] |SELECT array_unique(split('login,view,login,purchase,view,login', ',')) AS distinct_events;
| distinct_events |
| ----------------------- |
| [login, view, purchase] |SELECT cardinality(array_unique(ARRAY['US', 'US', 'MX', 'US', 'BR', 'MX'])) AS distinct_card_countries;
| distinct_card_countries |
| ----------------------- |
| 3 |SELECT array_value('electronics', 'books', 'toys') AS categories;
| categories |
| -------------------------- |
| [electronics, books, toys] |SELECT contains(array_value('US', 'CA', 'GB'), upper('ca')) AS is_supported_country;
| is_supported_country |
| -------------------- |
| true |SELECT array_max(array_value(0.12, 0.87, 0.35)) AS max_risk_signal;
| max_risk_signal |
| --------------- |
| 0.87 |Type parameters. T means matching inputs and outputs use the same type.
SELECT arrays_overlap(split(lower('Wire transfer to CRYPTO exchange'), ' '), ARRAY['crypto', 'casino', 'gift']) AS memo_mentions_risky_merchant;
| memo_mentions_risky_merchant |
| ---------------------------- |
| true |SELECT arrays_overlap(ARRAY['electronics', 'gaming'], ARRAY['groceries', 'pharmacy']) AS shares_category;
| shares_category |
| --------------- |
| false |Type parameters. T means matching inputs and outputs use the same type.
SELECT arrays_union(ARRAY['electronics', 'gift', 'sale'], ARRAY['sale', 'clearance', 'gift']) AS merged_tags;
| merged_tags |
| ------------------------------------ |
| [electronics, gift, sale, clearance] |SELECT cardinality(arrays_union(ARRAY['ios', 'web', 'ios'], ARRAY['web', 'android'])) AS distinct_device_platforms;
| distinct_device_platforms |
| ------------------------- |
| 3 |array or entries in a map.Type parameters. V is the value type of the input map. K is the key type of the input map.
SELECT cardinality(array_constructor(1, 2, 3));
| cardinality(array_constructor(1, 2, 3)) |
| --------------------------------------- |
| 3 |array.Type parameters. T means matching inputs and outputs use the same type.
SELECT combinations(array_sort(array_distinct(ARRAY['laptop','mouse','laptop','usb_hub'])), 2) AS basket_pairs;
| basket_pairs |
| ------------------------------------------------------ |
| [[laptop, mouse], [laptop, usb_hub], [mouse, usb_hub]] |SELECT transform(combinations(ARRAY['card','wallet','bank'], 2), p -> array_join(p, '+')) AS channel_pairs;
| channel_pairs |
| ------------------------------------- |
| [card+wallet, card+bank, wallet+bank] |SELECT contains(split(lower('Wire transfer for CRYPTO exchange deposit'), ' '), 'crypto') AS memo_mentions_crypto;
| memo_mentions_crypto |
| -------------------- |
| true |SELECT contains(ARRAY[7995, 6051, 4829], 5411) AS is_high_risk_mcc;
| is_high_risk_mcc |
| ---------------- |
| false |array or map.Type parameters. E is the element type of the input array or list. K is the key type of the input map. V is the value type of the input map.
SELECT element_at(array_constructor('a', 'b'), 2);
| element_at(array_constructor('a', 'b'), 2) |
| ------------------------------------------ |
| b |array that matches the given predicate function.Type parameters. T means matching inputs and outputs use the same type.
SELECT find_first(ARRAY[120.0, 45.5, 980.0, 2500.0, 60.0], x -> x > 500) AS first_large_txn;
| first_large_txn |
| --------------- |
| 980.0 |SELECT find_first(ARRAY['gmail.com', 'acme-corp.com', 'yahoo.com'], d -> d NOT IN ('gmail.com', 'yahoo.com', 'hotmail.com')) AS work_domain;
| work_domain |
| ------------- |
| acme-corp.com |array that matches the given predicate function.Type parameters. T means matching inputs and outputs use the same type.
SELECT find_first_index(ARRAY[42.10, 18.75, 1250.00, 9.99, 3400.00], x -> x > 1000) AS first_large_txn_position;
| first_large_txn_position |
| ------------------------ |
| 3 |SELECT find_first_index(ARRAY['viewed', 'added_to_cart', 'viewed', 'purchased'], e -> e = 'purchased') AS steps_to_purchase;
| steps_to_purchase |
| ----------------- |
| 4 |array by one level, combining all sub-arrays into a single array.Type parameters. T means matching inputs and outputs use the same type.
SELECT flatten(ARRAY[ARRAY['electronics', 'audio'], ARRAY['audio', 'accessories'], ARRAY['books']]) AS order_categories;
| order_categories |
| ----------------------------------------------- |
| [electronics, audio, audio, accessories, books] |SELECT flatten(ARRAY[ARRAY[ARRAY[1, 2], ARRAY[3]], ARRAY[ARRAY[4]]]) AS one_level;
| one_level |
| ------------------ |
| [[1, 2], [3], [4]] |Type parameters. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
SELECT greatest(3.0, 7.0, 5.0);
| greatest(3.0, 7.0, 5.0) |
| ----------------------- |
| 7.0 |SELECT greatest(3, 7, 5);
| greatest(3, 7, 5) |
| ----------------- |
| 7 |Type parameters. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
SELECT least(3.0, 7.0, 5.0);
| least(3.0, 7.0, 5.0) |
| -------------------- |
| 3.0 |SELECT least(3, 7, 5);
| least(3, 7, 5) |
| -------------- |
| 3 |SELECT list_apply(ARRAY[' Groceries ', 'GAS', 'groceries'], t -> lower(trim(t))) AS normalized_tags;
| normalized_tags |
| --------------------------- |
| [groceries, gas, groceries] |SELECT list_apply(ARRAY[12.5, 250.0, 4999.99], amt -> amt > 1000) AS is_large_txn;
| is_large_txn |
| -------------------- |
| [false, false, true] |SELECT list_avg(ARRAY[42.50, 18.99, 120.00, 7.25]) AS avg_txn_amount;
| avg_txn_amount |
| -------------- |
| 47.185 |SELECT list_avg(ARRAY[4, 5, NULL, 3, 5]) AS avg_review_rating;
| avg_review_rating |
| ----------------- |
| 4.25 |SELECT list_concat(ARRAY['electronics','laptops'], ARRAY['clearance'], ARRAY['back_to_school']) AS merged_tags;
| merged_tags |
| ------------------------------------------------- |
| [electronics, laptops, clearance, back_to_school] |SELECT array_max(list_concat(ARRAY[42.50, 18.00, 7.25], ARRAY[310.00, 299.99])) AS max_txn_amount;
| max_txn_amount |
| -------------- |
| 310.0 |SELECT list_contains(split(lower('Wire transfer to CRYPTO exchange'), ' '), 'crypto') AS memo_mentions_crypto;
| memo_mentions_crypto |
| -------------------- |
| true |SELECT list_contains(ARRAY[5411, 5812, 7995], 7995) AS is_gambling_mcc;
| is_gambling_mcc |
| --------------- |
| true |SELECT list_count(regexp_split_to_array('Refund request: duplicate charge on order #4821, card ending 1234', ' ')) AS memo_word_count;
| memo_word_count |
| --------------- |
| 10 |SELECT list_count(array_distinct(ARRAY['electronics', 'gift_card', 'electronics', 'jewelry'])) AS distinct_category_count;
| distinct_category_count |
| ----------------------- |
| 3 |SELECT list_distinct(regexp_split(lower('Refund refund REFUND card fee card'), ' ')) AS memo_tokens;
| memo_tokens |
| ------------------- |
| [refund, card, fee] |SELECT cardinality(list_distinct(ARRAY['ios', 'web', 'ios', 'android', 'web', 'ios'])) AS distinct_devices;
| distinct_devices |
| ---------------- |
| 3 |Type parameters. E is the element type of the input array or list. K is the key type of the input map. V is the value type of the input map.
SELECT list_extract(split('Amazon Mktplace*2K4LP1 Seattle WA', ' '), 1) AS merchant_token;
| merchant_token |
| -------------- |
| Amazon |SELECT list_extract(split('Amazon Mktplace*2K4LP1 Seattle WA', ' '), -1) AS state_code;
| state_code |
| ---------- |
| WA |SELECT list_extract(MAP(ARRAY['US', 'CA', 'MX'], ARRAY[0.02, 0.05, 0.11]), 'CA') AS country_risk;
| country_risk |
| ------------ |
| 0.05 |list using a callback function predicate.Type parameters. T means matching inputs and outputs use the same type.
SELECT list_filter([12.50, 899.00, 3.25, 1450.00, 64.10], amt -> amt > 500) AS large_transactions;
| large_transactions |
| ------------------ |
| [899.0, 1450.0] |SELECT list_filter(['wire transfer to acct', 'grocery store', 'crypto exchange deposit', 'coffee'], memo -> memo LIKE '%wire%' OR memo LIKE '%crypto%') AS risky_memos;
| risky_memos |
| ------------------------------------------------ |
| [wire transfer to acct, crypto exchange deposit] |SELECT cardinality(list_filter(['success', 'failed', 'failed', 'success', 'failed'], status -> status = 'failed')) AS failed_login_count;
| failed_login_count |
| ------------------ |
| 3 |SELECT list_has(split(lower('POS PURCHASE ACH REVERSAL 0423'), ' '), 'reversal') AS is_reversal;
| is_reversal |
| ----------- |
| true |SELECT list_has(ARRAY[200, 201, 204], 404) AS is_success_status;
| is_success_status |
| ----------------- |
| false |SELECT list_has_any(['refund', 'chargeback', 'dispute'], split(lower('Customer filed a DISPUTE for order 4412'), ' ')) AS mentions_dispute_term;
| mentions_dispute_term |
| --------------------- |
| true |SELECT list_has_any(['gift_cards', 'crypto', 'wire_transfer'], ['electronics', 'books', 'groceries']) AS has_high_risk_category;
| has_high_risk_category |
| ---------------------- |
| false |SELECT cardinality(list_intersect(split(lower('Refund for DUPLICATE charge, possible fraud'), ' '), ARRAY['refund','chargeback','dispute','duplicate','fraud'])) AS dispute_keyword_hits;
| dispute_keyword_hits |
| -------------------- |
| 3 |SELECT list_intersect(ARRAY[101, 205, 205, 330, 412], ARRAY[205, 412, 412, 999]) AS shared_merchant_ids;
| shared_merchant_ids |
| ------------------- |
| [205, 412] |SELECT list_max(ARRAY[42.50, 1299.99, 18.75, 310.00]) AS largest_purchase;
| largest_purchase |
| ---------------- |
| 1299.99 |SELECT list_max(ARRAY['2026-03-14', '2026-09-30', '2026-07-02']) AS latest_login_date;
| latest_login_date |
| ----------------- |
| 2026-09-30 |SELECT list_median([42.5, 18.0, 1250.0, 37.25, 29.99]) AS median_order_amount;
| median_order_amount |
| ------------------- |
| 37.25 |SELECT list_median([12, 45, 8, 30]) AS median_session_minutes;
| median_session_minutes |
| ---------------------- |
| 21.0 |SELECT list_min(ARRAY[42.50, 18.99, 7.25, 103.00]) AS smallest_txn_amount;
| smallest_txn_amount |
| ------------------- |
| 7.25 |SELECT list_min(ARRAY[DATE '2026-03-14', DATE '2025-11-02', DATE '2026-01-20']) AS first_order_date;
| first_order_date |
| ---------------- |
| 2025-11-02 |SELECT array_mode(array_constructor(1, 2, 2, 3));
| array_mode(array_constructor(1, 2, 2, 3)) |
| ----------------------------------------- |
| 2 |SELECT remove_nulls(list_pack(CASE WHEN 950.0 > 500 THEN 'large_amount' END, CASE WHEN 'NG' <> 'US' THEN 'foreign_ip' END, CASE WHEN 2 > 5 THEN 'high_velocity' END)) AS risk_flags;
| risk_flags |
| -------------------------- |
| [large_amount, foreign_ip] |SELECT array_max(list_pack(120.0, 87.5, 342.0, 15.25)) AS max_txn_amount;
| max_txn_amount |
| -------------- |
| 342.0 |SELECT list_position(ARRAY['card_present', 'ecommerce', 'atm_withdrawal', 'wire'], 'atm_withdrawal') AS channel_index;
| channel_index |
| ------------- |
| 3 |SELECT list_position(ARRAY['low', 'medium', 'high', 'critical'], 'unknown') AS severity_rank;
| severity_rank |
| ------------- |
| 0 |SELECT list_slice(ARRAY[12.50, 8.99, 240.00, 15.25, 980.00, 1250.00], -3, -1) AS last_3_txn_amounts;
| last_3_txn_amounts |
| ---------------------- |
| [15.25, 980.0, 1250.0] |SELECT list_slice(ARRAY['login','view_item','add_to_cart','view_item','checkout','purchase'], 1, 6, 2) AS every_other_event;
| every_other_event |
| ------------------------------ |
| [login, add_to_cart, checkout] |SELECT list_slice('ACCT-2024-00918', 6, 9) AS account_year;
| account_year |
| ------------ |
| 2024 |SELECT list_sort(ARRAY[42.50, NULL, 7.99, 120.00, 15.25]) AS sorted_amounts;
| sorted_amounts |
| -------------------------------- |
| [7.99, 15.25, 42.5, 120.0, NULL] |SELECT slice(list_sort(ARRAY[42.50, 7.99, 120.00, 15.25, 89.00], x -> -x), 1, 3) AS top_3_amounts;
| top_3_amounts |
| ------------------- |
| [120.0, 89.0, 42.5] |SELECT round(list_stddev_pop([12.5, 48.0, 9.99, 130.0, 22.75], false), 2) AS txn_amount_stddev;
| txn_amount_stddev |
| ----------------- |
| 44.74 |SELECT round(list_stddev_pop([12.5, 48.0, 9.99, 130.0, 22.75], true), 2) AS txn_amount_sample_stddev;
| txn_amount_sample_stddev |
| ------------------------ |
| 50.03 |SELECT list_sum(ARRAY[3, 0, 7, 2]) AS failed_logins_last_4h;
| failed_logins_last_4h |
| --------------------- |
| 12 |SELECT list_sum(ARRAY[19.99, NULL, 5.01]) AS cart_total;
| cart_total |
| ---------- |
| 25.0 |SELECT list_transform(split(' Electronics , HOME_garden,Toys ', ','), t -> lower(trim(t))) AS normalized_tags;
| normalized_tags |
| -------------------------------- |
| [electronics, home_garden, toys] |SELECT list_transform(ARRAY[1299, 450, 8000], cents -> cents / 100.0) AS line_item_dollars;
| line_item_dollars |
| ------------------ |
| [12.99, 4.5, 80.0] |SELECT list_unique(split('electronics,books,electronics,toys,books', ',')) AS purchased_categories;
| purchased_categories |
| -------------------------- |
| [electronics, books, toys] |SELECT cardinality(list_unique(ARRAY['dev-a1', 'dev-b7', 'dev-a1', 'dev-a1', 'dev-c3'])) AS distinct_devices_used;
| distinct_devices_used |
| --------------------- |
| 3 |SELECT list_value(0.42, 1.0, 3.7) AS score_vector;
| score_vector |
| ---------------- |
| [0.42, 1.0, 3.7] |SELECT cardinality(array_distinct(list_value('visa', 'mastercard', 'visa', 'amex'))) AS distinct_card_networks;
| distinct_card_networks |
| ---------------------- |
| 3 |n) from an array.Type parameters. T means matching inputs and outputs use the same type.
SELECT transform(ngrams(split(lower('WIRE TRANSFER INTL FEE'), ' '), 2), g -> array_join(g, ' ')) AS memo_bigrams;
| memo_bigrams |
| ---------------------------------------- |
| [wire transfer, transfer intl, intl fee] |SELECT contains(ngrams(ARRAY['login', 'reset_password', 'change_email', 'withdraw', 'logout'], 3), ARRAY['reset_password', 'change_email', 'withdraw']) AS has_takeover_pattern;
| has_takeover_pattern |
| -------------------- |
| true |SELECT ngrams(ARRAY['login', 'reset_password', 'change_email'], 5) AS short_session_ngrams;
| short_session_ngrams |
| --------------------------------------- |
| [[login, reset_password, change_email]] |index in a list.Type parameters. E is the element type of the input array or list.
SELECT python_element_at(split('Home > Electronics > Audio > Headphones', ' > '), -1) AS leaf_category;
| leaf_category |
| ------------- |
| Headphones |SELECT python_element_at(ARRAY['login','browse','add_to_cart','checkout'], 0) AS first_event;
| first_event |
| ----------- |
| login |SELECT transform(python_range(0, length('STARBUCKS') - 2), i -> substr('STARBUCKS', i + 1, 3)) AS merchant_trigrams;
| merchant_trigrams |
| ----------------------------------- |
| [STA, TAR, ARB, RBU, BUC, UCK, CKS] |SELECT python_range(90, 0, -30) AS lookback_days;
| lookback_days |
| ------------- |
| [90, 60, 30] |array and returns the accumulated value.Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.
array or list. T means matching inputs and outputs use the same type.T means matching inputs and outputs use the same type. U is a second generic value type.Type parameters. E is the element type of the input array or list.
SELECT python_zip(ARRAY['groceries', 'fuel', 'dining', 'travel'], ARRAY[0.42, 0.31]) AS category_shares;
| category_shares |
| ------------------------------------------------- |
| [{f0: groceries, f1: 0.42}, {f0: fuel, f1: 0.31}] |SELECT transform(python_zip(ARRAY[100, 105, 98, 120], slice(ARRAY[100, 105, 98, 120], 2, 3)), p -> p.f1 - p.f0) AS balance_deltas;
| balance_deltas |
| -------------- |
| [5, -7, 22] |SELECT range(0, 25, 5) AS score_bucket_edges;
| score_bucket_edges |
| ------------------ |
| [0, 5, 10, 15, 20] |SELECT transform(range(0, 30, 7), d -> date_add('day', -d, DATE '2024-03-31')) AS weekly_lookback_dates;
| weekly_lookback_dates |
| ----------------------------------------------------------------------------------------------------------------------------- |
| [2024-03-31 00:00:00 UTC, 2024-03-24 00:00:00 UTC, 2024-03-17 00:00:00 UTC, 2024-03-10 00:00:00 UTC, 2024-03-03 00:00:00 UTC] |SELECT range(10, 0, -3) AS countdown;
| countdown |
| ------------- |
| [10, 7, 4, 1] |array.Type parameters. T means matching inputs and outputs use the same type.
SELECT remove_nulls(ARRAY[CASE WHEN 0.92 > 0.8 THEN 'high_risk_score' END, CASE WHEN 'US' <> 'NG' THEN 'geo_mismatch' END, CASE WHEN 3 > 5 THEN 'velocity_spike' END]) AS fired_rules;
| fired_rules |
| ------------------------------- |
| [high_risk_score, geo_mismatch] |SELECT cardinality(remove_nulls(ARRAY['+1-415-555-0100', NULL, 'alice@example.com', NULL])) AS verified_contact_count;
| verified_contact_count |
| ---------------------- |
| 2 |array.Type parameters. T means matching inputs and outputs use the same type.
SELECT slice(shuffle(ARRAY['sku_101', 'sku_202', 'sku_303', 'sku_404', 'sku_505']), 1, 2) AS sampled_recs;
| sampled_recs |
| ------------------ |
| [sku_303, sku_101] |SELECT array_sort(shuffle(ARRAY['sku_303', 'sku_101', 'sku_505', 'sku_202'])) = array_sort(ARRAY['sku_303', 'sku_101', 'sku_505', 'sku_202']) AS is_permutation;
| is_permutation |
| -------------- |
| true |length.Type parameters. E is the element type of the input array or list.
SELECT slice(ARRAY['login', 'view_item', 'add_to_cart', 'checkout', 'purchase'], 1, 3) AS first_three_events;
| first_three_events |
| ------------------------------- |
| [login, view_item, add_to_cart] |SELECT slice(ARRAY[120.0, 45.5, 300.0, 18.25, 999.99, 62.0], -3, 3) AS last_three_amounts;
| last_three_amounts |
| --------------------- |
| [18.25, 999.99, 62.0] |SELECT array_join(slice(split('Wireless Noise Cancelling Over-Ear Headphones with Mic', ' '), 1, 3), ' ') AS title_prefix;
| title_prefix |
| ------------------------- |
| Wireless Noise Cancelling |index in the array.Type parameters. T means matching inputs and outputs use the same type.
SELECT subscript(split('jane.doe@acme-corp.com', '@'), 2) AS email_domain;
| email_domain |
| ------------- |
| acme-corp.com |SELECT subscript(split('/shop/electronics/audio/headphones', '/'), cardinality(split('/shop/electronics/audio/headphones', '/'))) AS leaf_category;
| leaf_category |
| ------------- |
| headphones |function to each element in an array and returns a new array.Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.
SELECT transform(ARRAY[' AMZN Mktp US ', 'Uber *Trip', 'STARBUCKS #1234'], m -> upper(trim(m))) AS merchant_keys;
| merchant_keys |
| ------------------------------------------- |
| [AMZN MKTP US, UBER *TRIP, STARBUCKS #1234] |SELECT transform(ARRAY[12.50, 840.00, 45.10, 2300.00], amt -> CASE WHEN amt >= 500 THEN 'high' ELSE 'normal' END) AS amount_buckets;
| amount_buckets |
| ---------------------------- |
| [normal, high, normal, high] |n elements from the array.Type parameters. T means matching inputs and outputs use the same type.
SELECT trim_array(ARRAY['login', 'view_cart', 'checkout', 'heartbeat', 'heartbeat'], 2) AS user_actions;
| user_actions |
| ---------------------------- |
| [login, view_cart, checkout] |SELECT array_average(trim_array(ARRAY[120.0, 95.5, 143.0, 110.25, 18.0], 1)) AS avg_complete_day_spend;
| avg_complete_day_spend |
| ---------------------- |
| 117.1875 |Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.
SELECT zip(ARRAY['amount', 'velocity', 'geo_mismatch'], ARRAY[0.42, 0.31, 0.27]) AS feature_weights;
| feature_weights |
| -------------------------------------------------------------------------------- |
| [{f1: amount, f2: 0.42}, {f1: velocity, f2: 0.31}, {f1: geo_mismatch, f2: 0.27}] |SELECT zip(ARRAY['ord_1001', 'ord_1002', 'ord_1003'], ARRAY[5, 4]) AS order_ratings;
| order_ratings |
| ------------------------------------------------------------------------ |
| [{f1: ord_1001, f2: 5}, {f1: ord_1002, f2: 4}, {f1: ord_1003, f2: NULL}] |function to transform paired elements.Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type. R is the callback result type.
T means matching inputs and outputs use the same type.function applied to the elements. T means matching inputs and outputs use the same type. U is a second generic value type. R is the callback result type.SELECT zip_with(ARRAY[2, 1, 3], ARRAY[4.99, 19.50, 2.25], (qty, price) -> qty * price) AS line_totals;
| line_totals |
| ------------------ |
| [9.98, 19.5, 6.75] |SELECT zip_with(ARRAY['US', 'GB', 'DE'], ARRAY['US', 'FR'], (billing, shipping) -> billing = shipping) AS country_matches;
| country_matches |
| ------------------- |
| [true, false, NULL] |Type parameters. E is the element type of the input array or list.
SELECT split(lower('Wire transfer to Binance via Coinbase'), ' ') && ARRAY['binance','coinbase','kraken'] AS mentions_crypto_exchange;
| mentions_crypto_exchange |
| ------------------------ |
| true |SELECT ARRAY['US','CA'] && ARRAY['KP','IR','SY'] AS ships_to_sanctioned_country;
| ships_to_sanctioned_country |
| --------------------------- |
| false |SELECT boost_regexp_findall('[A-Z]{3}-[0-9]{4}', 'Blocked by ENG-1042; see also OPS-0077 and ENG-1190') AS ticket_refs;
| ticket_refs |
| ------------------------------ |
| [ENG-1042, OPS-0077, ENG-1190] |SELECT boost_regexp_findall('[$]([0-9]+[.][0-9]{2})', 'UBER TRIP $23.40 + tip $5.00, refund $12.15') AS amounts;
| amounts |
| -------------------- |
| [23.40, 5.00, 12.15] |string using the Boyer-Moore algorithm. Returns the 0-based byte offset of the first match, or -1 if not found.SELECT transform(ARRAY['REFUND AMAZON MKTPL', 'ACH DEBIT PAYROLL ACME CORP', 'POS REFUND TARGET #1123'], memo -> boyer_moore_search(memo, 'REFUND')) AS refund_offsets;
| refund_offsets |
| -------------- |
| [0, -1, 4] |SELECT substr('POS PURCHASE 04/12 WHOLEFDS MKT #10234 AUSTIN TX', boyer_moore_search('POS PURCHASE 04/12 WHOLEFDS MKT #10234 AUSTIN TX', '#') + 2, 5) AS store_number;
| store_number |
| ------------ |
| 10234 |SELECT replace('WIRE' || chr(9) || 'ACME CORP' || chr(9) || 'INV-2231', chr(9), ' | ') AS cleaned_memo;
| cleaned_memo |
| ----------------------------- |
| WIRE \| ACME CORP \| INV-2231 |SELECT chr(65 + CAST(floor(0.47 * 5) AS BIGINT)) AS risk_grade;
| risk_grade |
| ---------- |
| C |string.SELECT codepoint(substr('Émile Zola', 1, 1)) AS first_letter_codepoint;
| first_letter_codepoint |
| ---------------------- |
| 201 |SELECT codepoint(substr('🛒 Grocery run', 1, 1)) > 127 AS memo_starts_non_ascii;
| memo_starts_non_ascii |
| --------------------- |
| true |string.SELECT count_matches('Refund not received. REFUND requested twice; please refund ASAP', '(?i)\brefund') AS refund_mentions;
| refund_mentions |
| --------------- |
| 3 |SELECT count_matches('Call me at 415-555-0123 or 415-555-0199, email jo@example.com', '\d{3}-\d{3}-\d{4}') AS phone_numbers;
| phone_numbers |
| ------------- |
| 2 |SELECT editdist3('Amazon Mktplace', 'Amazon Marketplace') AS memo_edit_distance;
| memo_edit_distance |
| ------------------ |
| 3 |SELECT round(1.0 - editdist3(lower('Jon Smith'), lower('JOHN SMITH')) / CAST(greatest(length('Jon Smith'), length('JOHN SMITH')) AS DOUBLE), 2) AS name_similarity;
| name_similarity |
| --------------- |
| 0.9 |string1 and string2, i.e. the number of positions at which the corresponding characters are different. Note that the two strings must have the same length.SELECT hamming_distance('paypal.com', 'paypa1.com') AS lookalike_domain_edits;
| lookalike_domain_edits |
| ---------------------- |
| 1 |SELECT hamming_distance('1HGCM82633A004352', '1HGCM82633A004325') AS vin_char_mismatches;
| vin_char_mismatches |
| ------------------- |
| 2 |SELECT ilike('UBER *TRIP HELP.UBER.COM', '%uber%trip%') AS is_rideshare;
| is_rideshare |
| ------------ |
| true |SELECT ilike('Order SKU: tsh-blk-m', '%TSH-___-M') AS is_medium_tshirt;
| is_medium_tshirt |
| ---------------- |
| true |SELECT 'RE: Refund request for order #4521' ILIKE '%refund%' AS is_refund_ticket;
| is_refund_ticket |
| ---------------- |
| true |SELECT strpos('chalk data', 'data');
| strpos('chalk data', 'data') |
| ---------------------------- |
| 7 |SELECT round(jaccard_similarity(lower('AMZN Mktp US'), lower('Amazon Marketplace')), 3) AS merchant_name_similarity;
| merchant_name_similarity |
| ------------------------ |
| 0.533 |SELECT jaccard_similarity('listen', 'silent') AS anagram_similarity;
| anagram_similarity |
| ------------------ |
| 1.0 |SELECT round(jaro_similarity(lower('Jon Smith'), lower('JOHN SMITH')), 3) AS name_match;
| name_match |
| ---------- |
| 0.967 |SELECT jaro_similarity(lower(trim(' Katherine Johnson')), lower('CATHERINE JOHNSON')) >= 0.9 AS payee_matches_account_holder;
| payee_matches_account_holder |
| ---------------------------- |
| true |SELECT round(jaro_winkler_distance(upper(trim(' Jonathon Smyth ')), 'JONATHAN SMITH', 0.1), 4) AS cardholder_name_distance;
| cardholder_name_distance |
| ------------------------ |
| 0.0571 |SELECT ARRAY[round(jaro_winkler_distance('MARTHA', 'MARHTA', 0.0), 4), round(jaro_winkler_distance('MARTHA', 'MARHTA', 0.1), 4), round(jaro_winkler_distance('MARTHA', 'MARHTA', 0.25), 4)] AS distance_by_prefix_weight;
| distance_by_prefix_weight |
| ------------------------- |
| [0.0556, 0.0389, 0.0139] |SELECT round(jaro_winkler_similarity(lower('Jonathan Smith'), lower('JONATHON SMITH')), 3) AS cardholder_name_match;
| cardholder_name_match |
| --------------------- |
| 0.971 |SELECT round(jaro_winkler_similarity(lower('Jonathan Smith'), lower('JONATHON SMITH'), 0.0), 3) AS match_without_prefix_bonus;
| match_without_prefix_bonus |
| -------------------------- |
| 0.952 |string using the Knuth-Morris-Pratt algorithm. Returns the 0-based byte offset of the first match, or -1 if not found.SELECT substr('ACH DEBIT VENMO PAYMENT REF:8841273', knuth_morris_pratt_search('ACH DEBIT VENMO PAYMENT REF:8841273', 'REF:') + 5) AS payment_ref;
| payment_ref |
| ----------- |
| 8841273 |SELECT knuth_morris_pratt_search(lower('Where is my REFUND for order 5512?'), 'refund') >= 0 AS mentions_refund;
| mentions_refund |
| --------------- |
| true |string, binary, array, or map.Type parameters. V is the value type of the input map.
SELECT length('chalk');
| length('chalk') |
| --------------- |
| 5 |SELECT length(array_constructor(1, 2, 3));
| length(array_constructor(1, 2, 3)) |
| ---------------------------------- |
| 3 |SELECT levenshtein('paypa1-secure.com', 'paypal-secure.com') AS email_domain_edit_distance;
| email_domain_edit_distance |
| -------------------------- |
| 1 |SELECT round(1.0 - CAST(levenshtein(lower('AMZN Mktplace'), lower('Amazon Marketplace')) AS DOUBLE) / greatest(length('AMZN Mktplace'), length('Amazon Marketplace')), 3) AS merchant_name_similarity;
| merchant_name_similarity |
| ------------------------ |
| 0.722 |SELECT levenshtein_distance('Jonathon Smyth', 'Jonathan Smith') AS name_edit_distance;
| name_edit_distance |
| ------------------ |
| 2 |SELECT round(1.0 - levenshtein_distance(lower('Acme Corp.'), lower('ACME Corporation')) / greatest(length('Acme Corp.'), length('ACME Corporation')), 3) AS merchant_name_similarity;
| merchant_name_similarity |
| ------------------------ |
| 0.563 |SELECT like('ACH DEBIT NETFLIX.COM 866-579-7172', '%NETFLIX%') AS is_netflix_charge;
| is_netflix_charge |
| ----------------- |
| true |SELECT like('SKU-48210-XL', 'SKU-____-XL') AS matches_sku_format;
| matches_sku_format |
| ------------------ |
| false |SELECT like('Save 20% on your next order', '%20#%%', '#') AS mentions_20_pct_off;
| mentions_20_pct_off |
| ------------------- |
| true |SELECT longest_common_subsequence(lower('AMZN MKTP US*2K4'), lower('amazon marketplace')) AS lcs_length;
| lcs_length |
| ---------- |
| 9 |SELECT round(CAST(longest_common_subsequence(lower('Jonathan Smith'), lower('JON SMITH')) AS DOUBLE) / greatest(length('Jonathan Smith'), length('JON SMITH')), 2) AS name_match_score;
| name_match_score |
| ---------------- |
| 0.64 |template using the provided JSON context via minijinja.SELECT minijinja_render('Classify this transaction memo: "{{ memo }}" (amount: ${{ amount }}, merchant: {{ merchant|upper }})', '{"memo": "UBER *TRIP 8XK2", "amount": 23.5, "merchant": "uber"}') AS llm_prompt;
| llm_prompt |
| --------------------------------------------------------------------------------- |
| Classify this transaction memo: "UBER *TRIP 8XK2" (amount: $23.5, merchant: UBER) |SELECT minijinja_render('{% if score >= 0.8 %}HIGH{% elif score >= 0.5 %}MEDIUM{% else %}LOW{% endif %} risk: {{ flags|join(", ") }}', '{"score": 0.86, "flags": ["new_device", "foreign_ip"]}') AS risk_summary;
| risk_summary |
| --------------------------------- |
| HIGH risk: new_device, foreign_ip |SELECT partial_ratio('starbucks', lower('SQ *STARBUCKS STORE #1234 SEATTLE WA')) AS merchant_match_score;
| merchant_match_score |
| -------------------- |
| 100.0 |SELECT round(partial_ratio('starbucks', lower('SQ *STARBUKS COFFEE SEATTLE WA')), 1) AS merchant_match_score;
| merchant_match_score |
| -------------------- |
| 88.9 |SELECT partial_ratio('starbucks', 'SQ *STARBUCKS STORE #1234 SEATTLE WA') AS case_sensitive_score;
| case_sensitive_score |
| -------------------- |
| 0.0 |SELECT starts_with('feature_store', 'feature');
| starts_with('feature_store', 'feature') |
| --------------------------------------- |
| true |string are alphanumeric and there is at least one character, matching Python's str.isalnum() behavior.SELECT transform(ARRAY['SAVE20', 'SAVE-20', 'save 20', ''], code -> python_is_alnum(code)) AS promo_code_is_alnum;
| promo_code_is_alnum |
| --------------------------- |
| [true, false, false, false] |SELECT transform(ARRAY['José99', '東京2024', 'x²', 'user_1'], handle -> python_is_alnum(handle)) AS handle_is_alnum;
| handle_is_alnum |
| ------------------------- |
| [true, true, true, false] |string are alphabetic and there is at least one character, matching Python's str.isalpha() behavior.SELECT transform(ARRAY['Zoë', 'Ελλάδα', 'O''Brien', 'Mary Ann', 'R2D2', ''], n -> python_is_alpha(n)) AS valid_given_name;
| valid_given_name |
| ---------------------------------------- |
| [true, true, false, false, false, false] |SELECT python_is_alpha(regexp_replace('Jean-Luc O''Néill', '[\s''-]', '')) AS full_name_is_letters;
| full_name_is_letters |
| -------------------- |
| true |SELECT python_is_digit(replace('4111-1111-1111-1111', '-', '')) AS is_numeric_card;
| is_numeric_card |
| --------------- |
| true |SELECT transform(ARRAY['94107', '9410A', '-42', '3.14', '', '²'], z -> python_is_digit(z)) AS is_digit_flags;
| is_digit_flags |
| ---------------------------------------- |
| [true, false, false, false, false, true] |SELECT transform(ARRAY['refund issued', 'URGENT: Call Me', 'order #48213 shipped', '12345'], s -> python_is_lower(s)) AS is_lower;
| is_lower |
| -------------------------- |
| [true, false, true, false] |SELECT CASE WHEN python_is_numeric(split_part('ACH PMT 4471 REF 20240115', ' ', 3)) THEN CAST(split_part('ACH PMT 4471 REF 20240115', ' ', 3) AS BIGINT) END AS payee_id;
| payee_id |
| -------- |
| 4471 |SELECT transform(ARRAY['94107', '12.50', '-3', '', '½', 'Ⅻ', '٣٤'], s -> python_is_numeric(s)) AS is_numeric;
| is_numeric |
| --------------------------------------------- |
| [true, false, false, false, true, true, true] |SELECT python_is_space(chr(9) || ' ' || chr(13) || chr(10)) AS ticket_body_is_blank;
| ticket_body_is_blank |
| -------------------- |
| true |SELECT transform(ARRAY[' ' || chr(9) || chr(10), '', ' refund pls '], memo -> python_is_space(memo)) AS memo_is_blank;
| memo_is_blank |
| -------------------- |
| [true, false, false] |SELECT python_is_space(chr(160) || chr(8195)) AS unicode_space;
| unicode_space |
| ------------- |
| true |string is titlecased and there is at least one cased character, matching Python's str.istitle() behavior.SELECT transform(ARRAY['Wireless Mouse', 'wireless mouse', 'USB-C Cable', 'iPhone Case', '2024 Model', 'ACME'], t -> python_is_title(t)) AS is_title_case;
| is_title_case |
| ---------------------------------------- |
| [true, false, false, false, true, false] |SELECT CASE WHEN python_is_title('Mary-Jane O''Brien') THEN 'clean' ELSE 'needs_normalization' END AS name_entry_quality;
| name_entry_quality |
| ------------------ |
| clean |SELECT python_is_upper('URGENT: REFUND ORDER #4471 NOW!!') AS is_all_caps_ticket;
| is_all_caps_ticket |
| ------------------ |
| true |SELECT python_is_upper('4471-0092') AS is_all_caps_ticket;
| is_all_caps_ticket |
| ------------------ |
| false |SELECT starts_with(python_lstrip(chr(160) || chr(9) || ' REFUND: duplicate charge'), 'REFUND') AS is_refund_memo;
| is_refund_memo |
| -------------- |
| true |SELECT '[' || python_lstrip(chr(160) || chr(12288) || ' Refund for order #8812 ') || ']' AS cleaned_memo;
| cleaned_memo |
| -------------------------- |
| [Refund for order #8812 ] |prefix from the string if present, matching Python's str.removeprefix().SELECT transform(ARRAY['POS DEBIT STARBUCKS #1123', 'ACH PAYROLL ACME INC'], m -> python_remove_prefix(m, 'POS DEBIT ')) AS merchants;
| merchants |
| --------------------------------------- |
| [STARBUCKS #1123, ACH PAYROLL ACME INC] |SELECT python_remove_prefix('Re: Re: Refund request #4821', 'Re: ') AS ticket_subject;
| ticket_subject |
| ------------------------ |
| Re: Refund request #4821 |suffix from the string if present, matching Python's str.removesuffix().SELECT python_remove_suffix('Acme Corp Inc.', ' Inc.') AS merchant_name;
| merchant_name |
| ------------- |
| Acme Corp |SELECT transform(ARRAY['events_2024.csv', 'events_2025.parquet', 'users.csv'], f -> python_remove_suffix(f, '.csv')) AS table_names;
| table_names |
| ----------------------------------------- |
| [events_2024, events_2025.parquet, users] |SELECT '[' || python_rstrip(' UBER *TRIP HELP.UBER.COM' || chr(160) || chr(12288) || chr(13) || chr(10)) || ']' AS cleaned_memo;
| cleaned_memo |
| ---------------------------- |
| [ UBER *TRIP HELP.UBER.COM] |SELECT python_rstrip('Wireless Earbuds' || chr(160)) = 'Wireless Earbuds' AS matches_catalog_title;
| matches_catalog_title |
| --------------------- |
| true |string with exact Python s[start:stop:step] semantics. All integer arguments are nullable (NULL = Python None / unspecified).SELECT python_string_slice('4111111111111111', -4, NULL, NULL) AS card_last4;
| card_last4 |
| ---------- |
| 1111 |SELECT python_string_slice('2024-03-15T09:30:00Z', 5, -10, NULL) AS month_day;
| month_day |
| --------- |
| 03-15 |SELECT python_string_slice('A1B2C3D4', 1, NULL, 2) AS digits;
| digits |
| ------ |
| 1234 |SELECT python_strip(chr(160) || ' Wireless Mouse - Black' || chr(9) || chr(10)) AS product_title;
| product_title |
| ---------------------- |
| Wireless Mouse - Black |SELECT python_strip(chr(160) || ' ACME COFFEE ' || chr(9) || chr(10)) = 'ACME COFFEE' AS memo_matches_merchant;
| memo_matches_merchant |
| --------------------- |
| true |SELECT nullif(python_strip(chr(12288) || chr(10) || chr(9) || ' '), '') IS NULL AS is_blank_note;
| is_blank_note |
| ------------- |
| true |SELECT regexp_full_match('POS PURCHASE STARBUCKS #4471 SEATTLE WA', '(?i)starbucks|dunkin|peet''s') AS is_coffee_shop;
| is_coffee_shop |
| -------------- |
| true |SELECT regexp_full_match('ORD-2024-00981-RETRY', '^ORD-[0-9]{4}-[0-9]{5}$') AS is_valid_order_id;
| is_valid_order_id |
| ----------------- |
| false |SELECT regexp_like('user_123', '^user_[0-9]+$');
| regexp_like('user_123', '^user_[0-9]+$') |
| ---------------------------------------- |
| true |SELECT regexp_split('POS PURCHASE*STARBUCKS #1234 SEATTLE WA', '[\s*#]+') AS memo_tokens;
| memo_tokens |
| --------------------------------------------- |
| [POS, PURCHASE, STARBUCKS, 1234, SEATTLE, WA] |SELECT cardinality(regexp_split('My order never arrived and support has not replied in 3 days', '\s+')) AS ticket_word_count;
| ticket_word_count |
| ----------------- |
| 12 |SELECT regexp_split_to_array('ACH DEBIT | PAYROLL;ACME CORP |ref#88213', '\s*[|;]\s*') AS memo_parts;
| memo_parts |
| ------------------------------------------ |
| [ACH DEBIT, PAYROLL, ACME CORP, ref#88213] |SELECT cardinality(regexp_split_to_array('Wireless noise-cancelling over-ear headphones', '[\s-]+')) AS title_token_count;
| title_token_count |
| ----------------- |
| 6 |SELECT round(sequence_matcher_ratio(lower(trim(' Jonathan Smith ')), lower(trim('JONATHAN SMYTH'))), 3) AS payee_name_similarity;
| payee_name_similarity |
| --------------------- |
| 0.929 |SELECT round(sequence_matcher_ratio('Jonathan Smith', 'JONATHAN SMITH'), 3) AS raw_ratio;
| raw_ratio |
| --------- |
| 0.214 |SELECT element_at(str_split('electronics>computers>laptops', '>'), -1) AS leaf_category;
| leaf_category |
| ------------- |
| laptops |SELECT str_split('ERROR: payment failed: card declined', ': ', 2) AS level_and_message;
| level_and_message |
| -------------------------------------- |
| [ERROR, payment failed: card declined] |SELECT str_split_regex('wireless headphones, noise-cancelling; bluetooth 5.3 |black', '\s*[,;|]\s*') AS product_tags;
| product_tags |
| ------------------------------------------------------------- |
| [wireless headphones, noise-cancelling, bluetooth 5.3, black] |SELECT str_split_regex('POS PURCHASE #4821 STARBUCKS STORE 1123', '\s*#\d+\s*')[2] AS merchant_part;
| merchant_part |
| -------------------- |
| STARBUCKS STORE 1123 |SELECT element_at(string_split('POS PURCHASE*STARBUCKS #1042*SEATTLE WA', '*'), 2) AS merchant_name;
| merchant_name |
| --------------- |
| STARBUCKS #1042 |SELECT string_split('DECLINED:card_issuer:Insufficient funds: retry after 24h', ':', 3) AS decline_parts;
| decline_parts |
| ------------------------------------------------------------ |
| [DECLINED, card_issuer, Insufficient funds: retry after 24h] |SELECT string_split_regex('organic, gluten-free;vegan | non-gmo', '\s*[,;|]\s*') AS product_tags;
| product_tags |
| -------------------------------------- |
| [organic, gluten-free, vegan, non-gmo] |SELECT element_at(string_split_regex('POS PURCHASE STARBUCKS #4821 SEATTLE WA', '\s{2,}'), 2) AS merchant_name;
| merchant_name |
| --------------- |
| STARBUCKS #4821 |SELECT element_at(string_to_array('Electronics > Computers > Laptops', ' > '), -1) AS leaf_category;
| leaf_category |
| ------------- |
| Laptops |SELECT string_to_array('POS PURCHASE*STARBUCKS #1234*SEATTLE WA', '*', 2) AS memo_parts;
| memo_parts |
| ------------------------------------------ |
| [POS PURCHASE, STARBUCKS #1234*SEATTLE WA] |SELECT json_extract_scalar(from_utf8(string_to_bytes_base64('eyJldmVudCI6ImNoYXJnZS5zdWNjZWVkZWQiLCJhbW91bnQiOjQyMDB9')), '$.event') AS event_type;
| event_type |
| ---------------- |
| charge.succeeded |SELECT length(string_to_bytes_base64('iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mNkYPhfDwAChwGA60e6kgAAAABJRU5ErkJggg==')) AS image_num_bytes;
| image_num_bytes |
| --------------- |
| 70 |SELECT string_to_bytes_hex('92ef9352c4a90c97d49718afa80788b91c407e18729a267a16b866d9bf3bc243') = sha256(to_utf8('{"order_id":1042,"amount":59.99}')) AS payload_digest_matches;
| payload_digest_matches |
| ---------------------- |
| true |SELECT from_utf8(string_to_bytes_hex('636172645f7465737465645f3432')) AS decoded_event;
| decoded_event |
| -------------- |
| card_tested_42 |SELECT length(string_to_bytes_utf8('Café ☕ München')) AS memo_byte_length;
| memo_byte_length |
| ---------------- |
| 18 |SELECT to_hex(sha256(string_to_bytes_utf8('user@example.com'))) AS email_sha256;
| email_sha256 |
| ---------------------------------------------------------------- |
| b4c9a289323b21a01c3e940f150eb9b8c542587f1abfd8f0e1cc1ffc5e475514 |length.SELECT substr('feature_store', 9);
| substr('feature_store', 9) |
| -------------------------- |
| store |SELECT substr('feature_store', 1, 7);
| substr('feature_store', 1, 7) |
| ----------------------------- |
| feature |SELECT substr('feature_store', 9);
| substr('feature_store', 9) |
| -------------------------- |
| store |SELECT substr('feature_store', 1, 7);
| substr('feature_store', 1, 7) |
| ----------------------------- |
| feature |SELECT ends_with('feature_store', 'store');
| ends_with('feature_store', 'store') |
| ----------------------------------- |
| true |SELECT token_set_ratio(lower('Starbucks Coffee Store 1234 Seattle'), lower('STARBUCKS COFFEE')) AS merchant_match_score;
| merchant_match_score |
| -------------------- |
| 100.0 |SELECT round(token_set_ratio(lower('Sony WH-1000XM5 Wireless Headphones Black'), lower('Sony WH-1000XM4 Wireless Headphones')), 1) AS title_similarity;
| title_similarity |
| ---------------- |
| 89.5 |SELECT token_set_ratio('Starbucks Coffee', 'STARBUCKS COFFEE') AS case_sensitive_score;
| case_sensitive_score |
| -------------------- |
| 18.75 |SELECT token_sort_ratio('john a smith', 'smith john a') AS name_match_score;
| name_match_score |
| ---------------- |
| 100.0 |SELECT token_sort_ratio(lower('Acme Corp Payroll Deposit'), lower('PAYROLL DEPOSIT ACME CORP')) >= 90 AS is_same_payee;
| is_same_payee |
| ------------- |
| true |N characters of the input string, up to at most the length of string.SELECT concat('**** **** **** ', trail('4111 1111 1111 4242', 4)) AS masked_card;
| masked_card |
| ------------------- |
| **** **** **** 4242 |SELECT trail('MX', 5) AS short_code_tail;
| short_code_tail |
| --------------- |
| MX |word using stemming algorithms.SELECT array_join(transform(split(lower('Customer requested refunds after charges were disputed'), ' '), w -> word_stem(w)), ' ') AS stemmed_memo;
| stemmed_memo |
| --------------------------------------------- |
| custom request refund after charg were disput |SELECT word_stem('Zahlungen', 'de') AS stem_de;
| stem_de |
| ------- |
| zahlung |string by delimiter and returns the part at the specified zero-based index.SELECT lower(zi_split_part('Jane.Doe@Example.COM', '@', 1)) AS email_domain;
| email_domain |
| ------------ |
| example.com |SELECT zi_split_part('POS 4821 STARBUCKS #1123 SEATTLE WA', ' ', 2) AS merchant;
| merchant |
| --------- |
| STARBUCKS |SELECT coalesce(zi_split_part('electronics>audio', '>', 2), 'none') AS third_level_category;
| third_level_category |
| -------------------- |
| none |SELECT starts_with('feature_store', 'feature');
| starts_with('feature_store', 'feature') |
| --------------------------------------- |
| true |SELECT 'MERCH-' || upper('acme coffee') || '-' || CAST(42 AS VARCHAR) AS merchant_key;
| merchant_key |
| -------------------- |
| MERCH-ACME COFFEE-42 |SELECT 'Title: ' || 'Wireless Earbuds' || ' | Brand: ' || coalesce(NULL, 'unknown') AS embedding_input;
| embedding_input |
| ----------------------------------------- |
| Title: Wireless Earbuds \| Brand: unknown |SELECT 'POS PURCHASE STARBUCKS #1234 SEATTLE WA' ~~ '%STARBUCKS%' AS is_coffee_purchase;
| is_coffee_purchase |
| ------------------ |
| true |SELECT lower('Pos Purchase STARBUCKS #1234') ~~ '%starbucks%' AS is_coffee_purchase;
| is_coffee_purchase |
| ------------------ |
| true |SELECT 'ORD-2024-A7' ~~ 'ORD-____-__' AS is_valid_order_id;
| is_valid_order_id |
| ----------------- |
| true |timestamp onto a fixed-width grid of `bucket_width_seconds`, anchored at `origin_seconds` (seconds since the Unix epoch).map as a list.Type parameters. K is the key type of the input map. V is the value type of the input map.
map. K is the key type of the input map. V is the value type of the input map.map as a list.Type parameters. K is the key type of the input map. V is the value type of the input map.
map. K is the key type of the input map. V is the value type of the input map.df1 (numerator degrees of freedom) and df2 (denominator degrees of freedom) parameters for the cumulative probability (p): P(N < n). The numerator and denominator df parameters must be positive real numbers. The probability p must lie on the interval [0, 1].value into json.Type parameters. T means matching inputs and outputs use the same type.
SELECT cast_to_json({'merchant': 'Blue Bottle', 'amount': 4.5, 'tags': ['coffee', 'recurring']}) AS txn_json;
| txn_json |
| --------------------------------------------------------------------- |
| {"amount":4.5,"merchant":"Blue Bottle","tags":["coffee","recurring"]} |SELECT cast_to_json('{"merchant": "Blue Bottle"}') AS memo_json;
| memo_json |
| ------------------------------------- |
| "{\\"merchant\\": \\"Blue Bottle\\"}" |SELECT get_json_value('{"customer": {"id": 4821, "tier": "gold"}, "items": [{"sku": "A-100", "qty": 2}]}', '$.customer.tier') AS customer_tier;
| customer_tier |
| ------------- |
| "gold" |SELECT get_json_value('{"order_id": "ord_771", "items": [{"sku": "A-100", "qty": 2}, {"sku": "B-205", "qty": 1}]}', '$.items[*].sku') AS order_skus;
| order_skus |
| ----------------- |
| ["A-100","B-205"] |SELECT CAST(get_json_value('{"txn": {"amount": 129.99, "currency": "USD"}}', '$.txn.amount') AS DOUBLE) * 100 AS amount_cents;
| amount_cents |
| ------------ |
| 12999.0 |SELECT get_json_value_strict('{"merchant": {"name": "Blue Bottle Coffee", "mcc": "5814"}, "amount": 6.25}', '$.merchant.mcc') AS merchant_mcc;
| merchant_mcc |
| ------------ |
| "5814" |SELECT CAST(get_json_value_strict('{"order_id": "ord_918", "items": [{"sku": "A1", "qty": 2}, {"sku": "B7", "qty": 1}], "total_cents": 4598}', '$.items[0].qty') AS BIGINT) AS first_item_qty;
| first_item_qty |
| -------------- |
| 2 |SELECT coalesce(CAST(get_json_value_strict('{"user_id": 42, "device": {"os": "ios"}}', '$.device.browser', false) AS VARCHAR), 'unknown') AS device_browser;
| device_browser |
| -------------- |
| unknown |SELECT transform(ARRAY['{"merchant": "Acme Grocery"}', '{"merchant": {"name": "Blue Bottle", "mcc": 5814}}'], p -> CASE WHEN is_json_scalar(json_extract(p, '$.merchant')) THEN json_extract_scalar(p, '$.merchant') ELSE json_extract_scalar(p, '$.merchant.name') END) AS merchant_name;
| merchant_name |
| --------------------------- |
| [Acme Grocery, Blue Bottle] |SELECT transform(ARRAY['42', '"refund"', 'true', 'null', '[1,2]', '{"a":1}'], x -> is_json_scalar(x)) AS is_scalar;
| is_scalar |
| -------------------------------------- |
| [true, true, true, true, false, false] |value exists in a JSON array.SELECT json_array_contains(json_extract(json_parse('{"ticket_id": 5521, "tags": ["billing", "refund", "urgent"]}'), '$.tags'), 'urgent') AS is_urgent;
| is_urgent |
| --------- |
| true |SELECT json_array_contains(json_parse('[12, 47, 305]'), '47') AS string_matches_number;
| string_matches_number |
| --------------------- |
| false |SELECT json_array_length(json_extract('{"order_id":"ord_918","items":[{"sku":"A1","qty":2},{"sku":"B2","qty":1},{"sku":"C3","qty":4}]}', '$.items')) AS line_item_count;
| line_item_count |
| --------------- |
| 3 |SELECT coalesce(json_array_length(json_extract('{"user_id":42}', '$.prior_chargebacks')), 0) AS prior_chargeback_count;
| prior_chargeback_count |
| ---------------------- |
| 0 |SELECT json_extract('{"order_id": "A-1042", "items": [{"sku": "MUG-01", "qty": 2}, {"sku": "TEE-XL", "qty": 1}]}', '$.items[*].sku') AS skus;
| skus |
| ------------------- |
| ["MUG-01","TEE-XL"] |SELECT CAST(json_extract('{"device": {"os": "iOS", "risk_score": 0.82}}', '$.device.risk_score') AS DOUBLE) > 0.7 AS high_risk_device;
| high_risk_device |
| ---------------- |
| true |SELECT cardinality(json_extract_array('{"txn_id":"t-77","risk_flags":["new_device","vpn","velocity"]}', '$.risk_flags')) AS risk_flag_count;
| risk_flag_count |
| --------------- |
| 3 |SELECT json_extract_array('{"order_id":"o-981","items":[{"sku":"SKU-1042","qty":2},{"sku":"SKU-2210","qty":1},{"sku":"SKU-1042","qty":1}]}', '$.items[*].sku') AS skus;
| skus |
| ------------------------------------ |
| ["SKU-1042", "SKU-2210", "SKU-1042"] |SELECT json_extract_path('{"event":"charge.succeeded","data":{"amount":4999,"card":{"brand":"visa","last4":"4242","country":"US"}}}', '$.data.card') AS card_details;
| card_details |
| ---------------------------------------------- |
| {"brand":"visa","country":"US","last4":"4242"} |SELECT json_extract_path('{"order_id":"A-1001","items":[{"sku":"MUG-01","qty":2},{"sku":"TEE-M","qty":1}]}', '$.items[1].sku') AS second_item_sku;
| second_item_sku |
| --------------- |
| "TEE-M" |SELECT json_extract_path_text('{"merchant": {"name": "Blue Bottle Coffee", "mcc": "5814"}, "amount": 6.75}', '$.merchant.mcc') AS merchant_mcc;
| merchant_mcc |
| ------------ |
| 5814 |SELECT json_extract_path_text('{"order_id": "ord_8812", "items": [{"sku": "A-100", "qty": 1}, {"sku": "B-200", "qty": 3}]}', '$.items[1].sku') AS second_item_sku;
| second_item_sku |
| --------------- |
| B-200 |SELECT CAST(json_extract_path_text('{"merchant": {"name": "Blue Bottle Coffee"}, "amount": 1250.00}', '$.amount') AS DOUBLE) > 1000 AS is_large_txn;
| is_large_txn |
| ------------ |
| true |SELECT CAST(json_extract_scalar('{"event": "charge.succeeded", "data": {"amount": 4999, "currency": "usd", "card": {"country": "CA"}}}', '$.data.amount') AS DOUBLE) / 100 AS amount_dollars;
| amount_dollars |
| -------------- |
| 49.99 |SELECT json_extract_scalar('{"order_id": "o-881", "items": [{"sku": "SKU-42", "qty": 2}, {"sku": "SKU-7", "qty": 1}]}', '$.items[0].sku') AS first_sku;
| first_sku |
| --------- |
| SKU-42 |SELECT json_extract_scalar('{"items": [{"sku": "SKU-42"}]}', '$.items') IS NULL AS array_path_is_null;
| array_path_is_null |
| ------------------ |
| true |SELECT json_extract_string('{"event": "charge.failed", "data": {"card": {"brand": "visa", "decline_code": "insufficient_funds"}}}', '$.data.card.decline_code') AS decline_code;
| decline_code |
| ------------------ |
| insufficient_funds |SELECT json_extract_string('{"items": [{"sku": "SHOE-42", "qty": 1}, {"sku": "SOCK-07", "qty": 3}]}', '$.items[1].sku') AS second_sku;
| second_sku |
| ---------- |
| SOCK-07 |SELECT coalesce(json_extract_string('{"device": {"os": "ios"}}', '$.device.browser'), 'unknown') AS browser;
| browser |
| ------- |
| unknown |SELECT json_format(json_extract(json_parse('{"order_id": "A-1009", "shipping": {"country": "US", "zip": "94107"}}'), '$.shipping')) AS shipping_json;
| shipping_json |
| ------------------------------ |
| {"country":"US","zip":"94107"} |SELECT json_format(json_parse('{ "merchant": "Blue Bottle", "amount": 4.50, "currency": "USD" }')) AS canonical_payload;
| canonical_payload |
| --------------------------------------------------------- |
| {"amount":4.50,"currency":"USD","merchant":"Blue Bottle"} |SELECT json_format(cast_to_json(MAP(ARRAY['failed_logins_1h', 'distinct_ips_24h'], ARRAY[3, 2]))) AS risk_signals_json;
| risk_signals_json |
| ------------------------------------------- |
| {"distinct_ips_24h":2,"failed_logins_1h":3} |SELECT transform(ARRAY['{"amount": 12.5, "merchant_id": null}', '{"amount": 40.0}', '{"amount": 9.99, "merchant_id": "m_881"}'], p -> json_is_null(p, '$.merchant_id')) AS merchant_explicitly_null;
| merchant_explicitly_null |
| ------------------------ |
| [true, false, false] |SELECT transform(ARRAY['{"kyc": {"ssn_last4": null}}', '{"kyc": {}}', '{"kyc": {"ssn_last4": "1234"}}'], p -> CASE WHEN json_is_null(p, '$.kyc.ssn_last4') THEN 'declined' WHEN json_extract_scalar(p, '$.kyc.ssn_last4') IS NULL THEN 'not_asked' ELSE 'provided' END) AS ssn_status;
| ssn_status |
| ------------------------------- |
| [declined, not_asked, provided] |stringSELECT json_parse('{"user_id": 42, "plan": "pro" , "seats": 5}') AS profile;
| profile |
| ------------------------------------- |
| {"plan":"pro","seats":5,"user_id":42} |SELECT json_extract_scalar(json_parse('{"event": "login", "device": {"os": "ios", "version": "17.2"}}'), '$.device.os') AS device_os;
| device_os |
| --------- |
| ios |SELECT json_size('{"order_id": "A-1042", "items": [{"sku": "TEE-M"}, {"sku": "MUG-01"}, {"sku": "CAP-L"}]}', '$.items') AS line_item_count;
| line_item_count |
| --------------- |
| 3 |SELECT json_size('{"device": {"os": "iOS", "model": "iPhone15", "locale": "en-US", "vpn": false}, "ip": "203.0.113.9"}', '$.device') AS device_attr_count;
| device_attr_count |
| ----------------- |
| 4 |SELECT json_stringify(json_parse('{"merchant": "Blue Bottle", "amount": 4.5, "tags": ["coffee", "food"]}')) AS payload;
| payload |
| ---------------------------------------------------------------- |
| {"amount":4.5,"merchant":"Blue Bottle","tags":["coffee","food"]} |SELECT json_stringify(json_extract(json_parse('{"user": {"id": 42, "tier": "gold"}, "event": "login"}'), '$.user')) AS user_json;
| user_json |
| ----------------------- |
| {"id":42,"tier":"gold"} |SELECT json_value('{"merchant": {"name": "Blue Bottle Coffee", "mcc": "5814"}, "amount": 6.75}', '$.merchant.mcc') AS merchant_mcc;
| merchant_mcc |
| ------------ |
| 5814 |SELECT CAST(json_value('{"order_id": "A-1042", "items": [{"sku": "TSHIRT-M", "qty": 2, "unit_price": 18.50}, {"sku": "MUG-01", "qty": 1, "unit_price": 12.00}]}', '$.items[0].unit_price') AS DOUBLE) * 2 AS first_line_total;
| first_line_total |
| ---------------- |
| 37.0 |value into a JSON stringType parameters. T means matching inputs and outputs use the same type.
SELECT jsonify({'txn_id': 'txn_8841', 'amount': 42.5, 'tags': ['grocery', 'card_present']}) AS txn_json;
| txn_json |
| --------------------------------------------------------------------- |
| {"amount":42.5,"tags":["grocery","card_present"],"txn_id":"txn_8841"} |SELECT jsonify({'user_id': 1017, 'risk_score': 0.82, 'is_flagged': true, 'last_login': NULL, 'shipping': {'country': 'US', 'zip': '94107'}}) AS user_json;
| user_json |
| ---------------------------------------------------------------------------------------------------------------- |
| {"is_flagged":true,"last_login":null,"risk_score":0.82,"shipping":{"country":"US","zip":"94107"},"user_id":1017} |SELECT transform(ARRAY['{"amount": 19.99}', '{"amount": 19.99', '{"amount": NaN}', ''], p -> orjson_json_is_valid(p)) AS payload_is_valid;
| payload_is_valid |
| --------------------------- |
| [true, false, false, false] |SELECT CASE WHEN orjson_json_is_valid('{"order_id": 1042, "risk_score": NaN}') THEN 'parse' ELSE 'quarantine' END AS webhook_route;
| webhook_route |
| ------------- |
| quarantine |SELECT CAST(python_json_array_get('[12.5, 40.0, 7.25]', -1) AS DOUBLE) AS last_amount;
| last_amount |
| ----------- |
| 7.25 |SELECT json_extract_scalar(python_json_array_get('[{"merchant": "Uber", "amount": 23.1}, {"merchant": "Steam", "amount": 59.99}]', -1), '$.merchant') AS latest_merchant;
| latest_merchant |
| --------------- |
| Steam |SELECT COALESCE(CAST(python_json_array_get('["card_present"]', -2) AS VARCHAR), 'no_prior_txn') AS prev_channel;
| prev_channel |
| ------------ |
| no_prior_txn |SELECT python_json_extract_bool('{"card": {"three_ds": {"authenticated": false}}, "risk_score": NaN}', 'card', 'three_ds', 'authenticated') AS three_ds_authenticated;
| three_ds_authenticated |
| ---------------------- |
| false |SELECT transform(ARRAY['{"is_fraud": true}', '{"is_fraud": "true"}', '{"is_fraud": 1}', '{}'], j -> python_json_extract_bool(j, 'is_fraud')) AS is_fraud_flags;
| is_fraud_flags |
| ------------------------ |
| [true, NULL, NULL, NULL] |SELECT python_json_extract_dict('{"device": {"fingerprint": {"os": "iOS", "jailbroken": false}}, "velocity": Infinity}', 'device', 'fingerprint') AS device_fingerprint;
| device_fingerprint |
| ---------------------------------- |
| {"os": "iOS", "jailbroken": false} |SELECT python_json_extract_dict('{"order_id": 981, "shipping": "pending"}', 'shipping') IS NULL AS shipping_not_a_dict;
| shipping_not_a_dict |
| ------------------- |
| true |SELECT python_json_extract_float('{"payment": {"amount": 129.99, "currency": "USD"}}', 'payment', 'amount') AS payment_amount;
| payment_amount |
| -------------- |
| 129.99 |SELECT COALESCE(python_json_extract_float('{"risk": {"score": "high"}}', 'risk', 'score'), -1.0) AS risk_score;
| risk_score |
| ---------- |
| -1.0 |SELECT python_json_extract_float('{"score": Infinity}', 'score') AS model_score;
| model_score |
| ----------- |
| inf |SELECT python_json_extract_int('{"order": {"id": "A-17", "quantity": 3, "discount": 0.15}}', 'order', 'quantity') AS quantity;
| quantity |
| -------- |
| 3 |SELECT ARRAY[python_json_extract_int('{"retries": 3}', 'retries'), python_json_extract_int('{"retries": "3"}', 'retries'), python_json_extract_int('{"retries": 3.0}', 'retries'), python_json_extract_int('{"retries": true}', 'retries')] AS int_or_null;
| int_or_null |
| --------------------- |
| [3, NULL, NULL, NULL] |SELECT python_json_extract_list('{"order": {"items": ["sku-1", "sku-2", "sku-3"]}}', 'order', 'items') AS order_items;
| order_items |
| --------------------------- |
| ["sku-1", "sku-2", "sku-3"] |SELECT coalesce(json_array_length(python_json_extract_list('{"customer": {"id": 42, "tags": "vip"}}', 'customer', 'tags')), 0) AS tag_count;
| tag_count |
| --------- |
| 0 |SELECT python_json_extract_string('{"payment": {"method": "card", "card": {"brand": "visa", "last4": "4242"}}, "amount": 1999}', 'payment', 'card', 'brand') AS card_brand;
| card_brand |
| ---------- |
| visa |SELECT coalesce(python_json_extract_string('{"shipping": {"country": "US", "zip": 94107}}', 'shipping', 'zip'), 'unknown') AS shipping_zip;
| shipping_zip |
| ------------ |
| unknown |SELECT python_json_is_valid('{"event": "payment.succeeded", "amount_cents": 1999, "currency": "usd"') AS webhook_is_parseable;
| webhook_is_parseable |
| -------------------- |
| false |SELECT transform(ARRAY['{"score": 0.91}', '{"score": NaN}', '{score: 0.5}', '{"score": 0.7,}'], p -> python_json_is_valid(p)) AS payload_is_valid;
| payload_is_valid |
| -------------------------- |
| [true, true, false, false] |keys, returning null for non-objects and missing keys.SELECT python_json_object_get('{"billing": {"address": {"country": "DE"}}}', 'billing', 'address', 'country') AS billing_country;
| billing_country |
| --------------- |
| "DE" |SELECT coalesce(CAST(python_json_object_get('{"user_id": "u_42", "profile": {"loyalty": ["gold"]}}', 'profile', 'loyalty', 'tier') AS VARCHAR), 'standard') AS loyalty_tier;
| loyalty_tier |
| ------------ |
| standard |SELECT python_json_size(json_extract(json_parse('{"order_id": 88, "line_items": [{"sku": "A"}, {"sku": "B"}, {"sku": "C"}]}'), '$.line_items')) AS line_item_count;
| line_item_count |
| --------------- |
| 3 |SELECT python_json_size(json_parse('{"device": {"os": "ios", "version": "17.4", "model": "iPhone15"}, "ip": "203.0.113.7", "tags": ["vip", "mobile"]}')) AS metadata_key_count;
| metadata_key_count |
| ------------------ |
| 3 |SELECT python_json_size(json_extract(json_parse('{"order_id": 88, "line_items": [{"sku": "A"}]}'), '$.order_id')) AS scalar_size;
| scalar_size |
| ----------- |
| 0 |SELECT python_json_object_get(python_json_value('{"merchant": "Blue Bottle", "fraud_score": NaN}'), 'merchant') AS merchant;
| merchant |
| ------------- |
| "Blue Bottle" |SELECT transform(ARRAY['{"amount": 42.0, "risk": NaN}', '{"amount": 12.5,}', 'None'], m -> python_json_value(m) IS NOT NULL) AS is_valid_payload;
| is_valid_payload |
| -------------------- |
| [true, false, false] |query against a policy via the regorus interpreter. Arguments are (policy, query, input_json, data_json); the result is the regorus QueryResults serialized as a JSON string.SELECT json_extract_scalar(regorus_eval('package txn
default allow := false
allow if { input.amount < 1000; input.country == "US" }', 'data.txn.allow', '{"amount": 250, "country": "US"}', ''), '$.result[0].expressions[0].value') AS allow;
| allow |
| ----- |
| true |SELECT json_extract(regorus_eval('package risk
reasons contains "amount_over_limit" if input.amount > data.limits.max_amount
reasons contains "blocked_country" if input.country in data.limits.blocked_countries
reasons contains "new_account" if input.account_age_days < 30', 'data.risk.reasons', '{"amount": 4200, "country": "KP", "account_age_days": 400}', '{"limits": {"max_amount": 2500, "blocked_countries": ["KP", "IR"]}}'), '$.result[0].expressions[0].value') AS flag_reasons;
| flag_reasons |
| --------------------------------------- |
| ["amount_over_limit","blocked_country"] |SELECT '{"family": "anatidae"}' ->> '$.family';
questions (noul/choice/score) about `state` and returns the answers as a JSON string alongside usage statistics.SELECT openai_complete('Classify this card transaction memo into exactly one category from [groceries, dining, travel, utilities, other]. Reply with the category only, lowercase. Memo: UBER *TRIP SAN FRANCISCO CA', model => 'gpt-4o-mini', temperature => 0.0, max_tokens => 5).completion AS memo_category;
| memo_category |
| ------------- |
| travel |SELECT openai_complete('Rate the urgency of this support ticket from 1 (low) to 5 (critical) and name its topic in one word. Ticket: Our checkout page has returned 500 errors for every customer since 9am.', model => 'gpt-4o-mini', temperature => 0.0, response_format => '{"type": "json_schema", "json_schema": {"name": "ticket", "strict": true, "schema": {"type": "object", "properties": {"urgency": {"type": "integer"}, "topic": {"type": "string"}}, "required": ["urgency", "topic"], "additionalProperties": false}}}').completion AS ticket_triage;
| ticket_triage |
| -------------------------------- |
| {"topic":"Checkout","urgency":5} |SELECT json_extract_scalar(openai_complete_raw('Classify this card transaction memo into one category (groceries, travel, dining, utilities). Reply with only the category: UBER *TRIP SAN FRANCISCO', model => 'gpt-4o-mini', temperature => 0.0), '$.choices[0].message.content') AS memo_category;
| memo_category |
| ------------- |
| travel |SELECT json_extract_scalar(openai_complete_raw('Summarize this support ticket in one sentence: My order #4821 arrived with a cracked screen and I need a replacement before Friday.', model => 'gpt-4o-mini', max_tokens => 5, temperature => 0.0), '$.choices[0].finish_reason') AS finish_reason;
| finish_reason |
| ------------- |
| length |SELECT round(cosine_similarity_vector(openai_embed('Wireless noise-cancelling headphones', model => 'text-embedding-3-small').embedding, openai_embed('Bluetooth over-ear headset with ANC', model => 'text-embedding-3-small').embedding), 1) AS title_similarity;
| title_similarity |
| ---------------- |
| 0.6 |SELECT cardinality(openai_embed('Wireless noise-cancelling headphones', model => 'text-embedding-3-small', dimensions => 256).embedding) AS embedding_dims;
| embedding_dims |
| -------------- |
| 256 |questions (noul/choice/score) about `state` and returns the answers as a JSON string alongside usage statistics.binary data to a structured format using an Avro schema.Type parameters. T means matching inputs and outputs use the same type.
T means matching inputs and outputs use the same type.binary data to a structured format.Type parameters. T means matching inputs and outputs use the same type.
T means matching inputs and outputs use the same type.binary data to a structured format, recording parse errors with row attribution.Type parameters. T means matching inputs and outputs use the same type.
T means matching inputs and outputs use the same type.integer into a 32-bit big-endian binary representation.bigint into a 64-bit big-endian binary representation.double as a 64-bit big-endian binary in IEEE 754 format.query directly against a registered datasource and returns the datasource result as a table. Use this when the SQL should be interpreted by the datasource dialect instead of the Chalk SQL dialect.SELECT * FROM datasource_query('my_postgres', 'SELECT id, email FROM public.users LIMIT 10');