1. What is FQL

FQL is a SQL-like query language that can be used within APL, enabling developers to query business data in the enterprise system.

2. FQL Syntax

SELECT  
  field1 [, field2 ...]  
[FROM object_apiName  
[WHERE where_condition]  
[ORDER BY {col_name | expr | position}  
  [ASC | DESC], ...]  
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

Notes:

• SELECT * is not supported. Fields must be explicitly specified: SELECT field1, field2...
• Default LIMIT is 10 (max 100), and default OFFSET is 0.
• OR conditions require additional product activation. Contact sales to order the feature: "Object List Filtering with OR Support".

3. Supported WHERE Operators

• AND Query:

SELECT name FROM object_227xW__c WHERE (field_rzv5M__c IS NULL AND field_rzv5M__c <= 100) ORDER BY _id DESC LIMIT 10 OFFSET 0;

• OR Query (AND has higher precedence than OR; use parentheses to prioritize OR):

SELECT name FROM object_227xW__c WHERE (field_rzv5M__c IS NULL OR field_rzv5M__c <= 100) ORDER BY _id DESC LIMIT 10 OFFSET 0;

• Equality/Inequality: =, !=

SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_oc43W__c = '13988523405' LIMIT 10 OFFSET 0;  
SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_oc43W__c != '13988523405' LIMIT 10 OFFSET 0;  
SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c = 100 LIMIT 10 OFFSET 0;  
SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c != 100 LIMIT 10 OFFSET 0;

• Greater/Less Than: >, <

SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c > 100 LIMIT 10 OFFSET 0;  
SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c < 100 LIMIT 10 OFFSET 0;

• Greater/Less Than or Equal: >=, <=

SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c >= 100 LIMIT 10 OFFSET 0;  
SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c <= 100 LIMIT 10 OFFSET 0;

• Wildcard Queries: LIKE, NOT LIKE (without % behaves as =)

SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_oc43W__c LIKE '%88523%';  
SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_oc43W__c LIKE '%88523';  
SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_oc43W__c LIKE '88523%';  
SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_oc43W__c NOT LIKE '%88523%';

• NULL Checks: IS NULL, IS NOT NULL

SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c IS NULL;  
SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c IS NOT NULL;

• Array Contains: @> (for List-type fields like single/multi-select)

SELECT _id, field_D8JyW__c, name FROM object_227xW__c WHERE field_D8JyW__c @> ARRAY['option1', 'option2'];

• Array Overlap: && (e.g., ARRAY[1,4,3] && ARRAY[2,1] returns true due to shared element 1)
  • Parentheses are required when combining && with other operators:

-- Incorrect (may cause parsing errors):  
SELECT _id, name FROM object_C0vxo__c WHERE create_time > 0 AND field_7cQ6y__c && ARRAY[1000];  
-- Correct:  
SELECT _id, name FROM object_C0vxo__c WHERE create_time > 0 AND (field_7cQ6y__c && ARRAY[1000]);  
SELECT _id FROM object_C0vxo__c WHERE field_7cQ6y__c && ARRAY[1000];

• IN/NOT IN:

SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c IN (21, 100);  
SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c NOT IN (21, 100);

• BETWEEN/NOT BETWEEN:

SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c BETWEEN 21 AND 100;  
SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c NOT BETWEEN 21 AND 100;

4. Advanced Usage

• Field Comparisons (not supported with IN, BETWEEN, or @>):

SELECT name FROM AccountObj WHERE last_modified_time > create_time;  
SELECT name FROM AccountObj WHERE name != firstName;  
SELECT name FROM AccountObj WHERE name = firstName;  
SELECT name FROM AccountObj WHERE name <= firstName;

• Aggregation Queries: Supports COUNT, SUM, MIN, MAX, AVG, and GROUP BY (no HAVING). Aggregations default to ES execution.

SELECT COUNT(1) FROM object_227xW__c WHERE field_qC2yp__c IS NOT NULL;  
SELECT SUM(field_rzv5M__c) FROM object_227xW__c WHERE field_qC2yp__c IS NOT NULL GROUP BY field_qC2yp__c;  
SELECT MAX(field_rzv5M__c) FROM object_227xW__c WHERE field_qC2yp__c IS NOT NULL GROUP BY field_qC2yp__c;  
SELECT MIN(field_rzv5M__c) FROM object_227xW__c WHERE field_qC2yp__c IS NOT NULL GROUP BY field_qC2yp__c;  
SELECT AVG(field_rzv5M__c) FROM object_227xW__c WHERE field_qC2yp__c IS NOT NULL GROUP BY field_qC2yp__c;  
-- Multiple aggregations/grouping:  
SELECT AVG(field_rzv5M__c), SUM(field_rzv5M__c) FROM object_227xW__c  
WHERE field_qC2yp__c IS NOT NULL GROUP BY field_qC2yp__c, record_type;

5. Code Examples

Fx.object.select:

• Returns QueryResult for standard queries.
• Returns List for aggregation queries.

// Standard query  
String sql = "SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c > 100 LIMIT 10 OFFSET 0;";  
def rst = Fx.object.select(sql).result() as QueryResult  
log.info(rst)  

// Exclude invalid records + return total count  
String sql1 = "SELECT _id, field_rzv5M__c, name FROM object_227xW__c WHERE field_rzv5M__c > 100 LIMIT 10 OFFSET 0;";  
SelectAttribute att = SelectAttribute.builder()  
  .needInvalid(false)  
  .build()  
def rst1 = Fx.object.select(sql1, att).result() as QueryResult  
log.info(rst1)