关于OQL
（一）什么是 OQL
OQL (Object Query Language) 是平台提供的一种针对面向对象数据库设计的查询语言标准，采用类似 SQL 的语法对记录进行 CRUD。目前低代码开发者可以在「函数」中以 SDK 的方式使用 OQL。
（二）和现有链式 DB 接口的区别
- 采用 SQL 风格的数据操作方式；
- 在链式接口的能力上有扩充，通过链式接口无法实现或实现起来比较复杂的数据操作，可通过 OQL 来实现。如聚合计算、Hierarchy 查询、查询结果的行内计算等。
使用方式
注意事项：
- 单次查询的数据量限制为 200（含聚合计算的查询为 1000）；
- 单次创建、更新、删除的数据量限制为 100；
- 字符串类型的值，需要使用单引号。
规则对照表
基础关键字
勾选项为已支持内容，未勾选项会陆续迭代支持
关键字 | 语法 | 示例 |
☑️ SELECT ☑️ FROM ☑️ WHERE ☑️ AS | SELECT expression [ [ AS ] alias ] [, ...] [ FROM object_source [ [ AS ] alias] ] [ WHERE expression ] | SELECT _name, age FROM employee WHERE age > 30; |
☑️ LIMIT ☑️ OFFSET | LIMIT [offset,] count [ OFFSET offset ] LIMIT 20; LIMIT 30,20; LIMIT 20 OFFSET 30; | SELECT _name, _createdAt, _updatedAt FROM _user LIMIT 20 OFFSET 30; |
| SELECT DISTINCT expression [, ...] | SELECT DISTINCT _name FROM _user; |
☑️ GROUP BY
支持的字段类型：
| GROUP BY fieldApiname [, ...]; HAVING condition; | SELECT service_id, COUNT(package_name) FROM cloud_function_npm_package GROUP BY service_id /* GROUP BY 位置引用（如下例，按 select 的第1 个 字段来 group by，可以避免重复写表达式） */ SELECT package_name, COUNT(package_name) FROM cloud_function_npm_package GROUP BY 1 /* GROUP BY alias */ SELECT package_name AS package, COUNT(package_name) FROM cloud_function_npm_package GROUP BY package |
☑️ ORDER BY ☑️ ASC ☑️ DESC ☑️ NULLS FIRST ☑️ NULLS LAST | ORDER BY expression [ ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...] | SELECT _name, _createdAt, _updatedAt FROM _user ORDER BY _createdAt, _updatedAt DESC /* ORDER BY 位置引用 */ SELECT _name, _createdAt, _updatedAt FROM _user ORDER BY 2, 3 DESC /* ORDER BY alias */ SELECT _name, _createdAt AS createAtAlias FROM _user ORDER BY createAtAlias |
☑️ Subquery
| SELECT expression [, ...] FROM table_name WHERE column IN (SELECT expression [, ...] FROM table_name WHERE condition); | SELECT Websites.name, Websites.url FROM Websites WHERE id (SELECT website_id FROM access_log AND count > 200); |
| SELECT expression [, ...] FROM table1 UNION SELECT expression [, ...] FROM table2; | SELECT _name, _email FROM _user WHERE _department._id=123 UNION SELECT manager._name, manager._email FROM project WHERE _id=456; |
☑️ COUNT ☑️ COUNT(DISTINCT) ☑️ SUM ☑️ AVG ☑️ MAX ☑️ MIN 支持的字段类型：
| SELECT COUNT(*) SELECT COUNT(fieldApiName) SELECT COUNT(DISTINCT fieldApiName) SELECT SUM(fieldApiName) SELECT AVG(fieldApiName) SELECT MAX(fieldApiName) SELECT MIN(fieldApiName) | SELECT COUNT(_id) FROM _user; |
☑️ ARRAY 把多个值组合成一个数组，值必须是相同类型 | ARRAY[value1, value2] | SELECT _id FROM _user WHERE HIERARCHY(_manager) && ARRAY[1001]; |
ROW 把多个值组合成一个数组，值可以是不同类型 | ROW(value1, value2) | |
☑️ AND ☑️ OR | boolValue1 AND boolValue2 boolValue1 OR boolValue2 | SELECT _name FROM _user WHERE age > 10 AND age < 20; |
☑️ NOT | NOT boolValue1 | SELECT _name FROM _user WHERE NOT (age > 10) |
☑️ IS NULL ☑️ IS NOT NULL | fieldApiName IS NULL | |
☑️ IS TRUE ☑️ IS NOT TRUE ☑️ IS FALSE ☑️ IS NOT FALSE ☑️ IS UNKNOWN ☑️ IS NOT UNKNOWN | fieldApiName IS TRUE | |
☑️ IS DISTINCT FROM ☑️ IS NOT DISTINCT FROM | fieldApiName IS DISTINCT FROM value1 | |
☑️ IN ☑️ NOT IN 支持的字段类型： IN：Text、Number、Option、Lookup NOT IN：Option、Lookup | IN (value1,value2,...); | SELECT _name FROM _user WHERE _id IN (123, 234, 345) |
☑️ BETWEEN ☑️ NOT BETWEEN | column_name BETWEEN value1 AND value2; | age BETWEEN 10 AND 20; |
☑️ LIKE ☑️ NOT LIKE 仅支持 % .. % 格式 | fieldApiName LIKE '%ab%' | SELECT _id FROM _user WHERE _name LIKE '%ab%'; |
☑️ AT TIME ZONE ☑️ AT LOCAL AT LOCAL 是依据用户当前时区 | AT TIME ZONE '+08:00' AT LOCAL | SELECT _createdAt AT TIME ZONE '+08:00' FROM _user |
☑️ CAST 支持转化为的类型有： STRING | BINARY | INT | DOUBLE | DECIMAL | ENUM | BOOLEAN | DATE | TIMESTAMP | TIMESTAMPTZ | objectName | objectName ID | ARRAY) 字段和数据类型对照表详见后文 | CAST(value1 AS type1) | SELECT CAST(_id AS STRING) FROM info |
☑️ EXTRACT | EXTRACT(part FROM datetimeValue1) | SELECT EXTRACT(YEAR FROM _createdAt) FROM _user |
☑️ CURRENT_DATE ☑️ CURRENT_TIME ☑️ CURRENT_TIMESTAMP ☑️ LOCALTIME ☑️ LOCALTIMESTAMP | CURRENT_DATE CURRENT_TIMESTAMP CURRENT_TIMESTAMP(3) | SELECT _id FROM _user WHERE _createdAt < CURRENT_TIMESTAMP |
☑️ INNER JOIN ☑️ LEFT JOIN ☑️ RIGHT JOIN ☑️ ON | objectApiName2 INNER JOIN objectApiName2 ON condition | SELECT customer_info.firstname, customer_info.lastname, purchases.item FROM customer_info INNER JOIN purchases ON customer_info.customer_number = purchases.customer_number; |
☑️ /* */ | /* 这是注释 */ | SELECT _id /* 这是注释 */ FROM _user |
☑️ INSERT INTO | INSERT INTO objectApiName (fieldApiName1, fieldApiName2, ...) VALUES (value1, value2, ...), (value1, value2, ...) [ RETURNING fieldApiName1, fieldApiName2, ... ] | INSERT INTO _user (_type, _manager) VALUES ('_employee', 1001) RETURNING _id |
☑️ UPDATE 必须有 where 条件 且 where 条件只能是 _id = 或 _id IN 或其他等价条件 | UPDATE objectApiName SET fieldApiName1 = value1, fieldApiName2 = value2, WHERE condition [ RETURNING fieldApiName1, fieldApiName2, ... ] | UPDATE _user SET _manager = 1002 WHERE _id = 1001 RETURNING _id |
☑️ DELETE FROM where 条件同 UPDATE | DELETE FROM objectApiName WHERE condition [ RETURNING fieldApiName1, fieldApiName2, ... ] | DELETE FROM _user WHERE _id = 1001 RETURNING _id |
特殊关键字
关键字 | 语法 | 示例 |
☑️ a.b.c ... 通过关联对象字段进行下钻 | lookupFieldApiName1.lookupFieldApiName2.fieldApiname1 | SELECT _id, _name, _department._superior._name AS superiorDepartmentID, HIERARCHY(_department) AS allDepartments FROM _user |
☑️ OBJECT 创建一个Object类型的值，用来如 INSERT 或 UPDATE 时组合类型字段的值 | OBJECT(fieldApiName1 := value1, fieldApiName2 := value2, ...) | |
☑️ HIERARCHY 获取全部层级结构记录 | HIERARCHY(hierarchyFieldApiName) | SELECT HIERARCHY(_manager) FROM _user SELECT _id FROM _user WHERE HIERARCHY(_manager) && ARRAY[10001] |
☑️ FIELD 根据字段元数据属性动态获取一个字段，如：_user 对象的层级字段是 _manager ，即 _manager 和 FIELD(HIERARCHY) 是等价的，所以 HIERARCHY(FIELD(HIERARCHY)) 就等于写 HIERARCHY(_manager)，好处是当对象的层级字段变了，OQL可以不用改。 | FIELD(DISPLAY_NAME) FIELD(AVATAR) FIELD(HIERARCHY) | SELECT FIELD(DISPLAY_NAME) FROM _user SELECT FIELD(HIERARCHY) FROM _user SELECT HIERARCHY(FIELD(HIERARCHY)) FROM _user |
☑️ DATE_FORMAT ☑️ TO_DATE 可以基于下图缩写清单进行格式组装 250px|700px|reset | DATE_FORMAT(datetimeValue1, 'YYYY-MM-DD') TO_DATE(stringValue1, 'YY-MM-DD') | SELECT DATE_FORMAT(_createdAt, 'YYYY-MM-DD') AS date FROM _user GROUP BY DATE_FORMAT (_createdAt, 'YYYY-MM-DD') |
操作符
操作符 | 语法 | 示例 |
☑️ [] ☑️ [:] | arrayValue[index] arrayValue[low:high] | |
☑️ = ☑️ <> != ☑️ < ☑️ > ☑️ <= ☑️ >= | fieldApiName > value1 | SELECT _id FROM _user WHERE age > 100; |
☑️ + ☑️ - ☑️ * ☑️ / ☑️ % | -numericValue1 numericValue1 + numericValue2 | |
☑️ && | multiValueFieldApiName && ARRAY[value1, value2, ...] | SELECT _id FROM info WHERE colors && ARRAY['red', 'green'] |
☑️ || 字符串拼接 | value1 || value2 | /* text 字段的所有返回结果后拼接字符串 '_suffix' */ SELECT text || '_suffix' FROM info |
字段数据类型
字段的值在 OQL 中会转换为以下数据类型，同样地，OQL 的返回结果会依据字段类型转换成等效的 JS 数据类型：
- 例如 OQL 的 JSON 类型自动转换成 JS 的 Object 或者 Array，其在 OQL 内部不是 Array，即不能用 Array 的操作符（如下标运算）。
字段类型 | OQL 数据类型 |
关联对象 lookup (单值) | Object ID |
文本 text 邮箱 email 自动编号 auto_number | STRING |
手机号码 phone | JSON |
数字 float | DOUBLE |
加密数字 encryptNumber | DECIMAL |
布尔 boolean | BOOLEAN |
选项 enum (单值) | ENUM |
日期 date | DATE |
日期时间 datetime | TIMESTAMPTZ |
选项 enum(多值) | STRING[] |
关联对象 lookup (多值) 反向关联 back_lookup | Object ID[] |
组合类型 compositeType (单值) 多值取单值 extractCompositeType | CompositeType |
组合类型 compositeType (多值) | CompositeType[] CompositeType ARRAY |
多语文本 multilingual | JSON |
富文本 richText 头像/标识 avatar 文件 attachment | JSON |
公式 formula | 返回值字段类型对应的数据类型 |
引用字段 reference_field | 引用的字段类型对应的数据类型 |
