OQL应用指南|飞书低代码平台

关于OQL

（一）什么是 OQL

OQL (Object Query Language) 是平台提供的一种针对面向对象数据库设计的查询语言标准，采用类似 SQL 的语法对记录进行 CRUD。目前低代码开发者可以在「函数」中以 SDK 的方式使用 OQL。

（二）和现有链式 DB 接口的区别

采用 SQL 风格的数据操作方式；

在链式接口的能力上有扩充，通过链式接口无法实现或实现起来比较复杂的数据操作，可通过 OQL 来实现。如聚合计算、Hierarchy 查询、查询结果的行内计算等。

使用方式

使用场景	使用场景	云函数
云函数	执行固定 OQL 语句： @kldx/core 版本需要 4.13.x 及以上 context.db.oql(oql).execute() 执行含参数的 OQL 语句： @byted-apaas/server-sdk-node 版本需要 1.0.9 及以上 context.db.oql(oql,nameArgs).execute() 参数： oql：OQL 语句（支持的关键字及操作符详见下方对照表） nameArgs：用于指定 OQL 语句中参数的具体值	// 执行固定 OQL 语句 const oql = "SELECT _id, _name FROM _user WHERE _type = '_employee' LIMIT 10"; const records = await context.db.oql(oql).execute(); // 执行含参数的 OQL 语句 const oql = `SELECT _id, _name FROM _user WHERE _type = $user_type AND _accountStatus = $user_status LIMIT 10` const records = await context.db.oql(oql, { user_type: "_employee", user_status: "_used", }).execute();
自定义组件	执行固定 OQL 语句： @byted-apaas/data版本需要1.8.0及以上 oql(oql) 执行含参数的 OQL 语句： @byted-apaas/data版本需要1.8.0及以上 oql(oql,nameArgs) 参数： oql：OQL 语句（支持的关键字及操作符详见下方对照表） nameArgs：用于指定 OQL 语句中参数的具体值	// 执行固定 OQL 语句 import { oql } from '@byted-apaas/data'; const oql = "SELECT _id, _name FROM _user WHERE _type = '_employee' LIMIT 10"; const records = await oql(oql); // 执行含参数的 OQL 语句 import { oql } from '@byted-apaas/data'; let employees = await oql("select _email from _user where _type = $user_type",{ * "user_type": "_employee", * });
Js自定义事件/HTML	执行固定 OQL 语句： application.data.oql(oql) 执行含参数的 OQL 语句： application.data.oql(oql,nameArgs) 参数： oql：OQL 语句（支持的关键字及操作符详见下方对照表） nameArgs：用于指定 OQL 语句中参数的具体值	// 执行固定 OQL 语句 const oql = "SELECT _id, _name FROM _user WHERE _type = '_employee' LIMIT 10"; const records = await application.data.oql(oql); // 执行含参数的 OQL 语句 let employees =await application.data.oql("select _email from _user where _type = $user_type",{ * "user_type": "_employee", * });

规则对照表

基础关键字

关键字	语法	示例
☑️ 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	SELECT DISTINCT expression [, ...]	SELECT DISTINCT _name FROM _user;
☑️ GROUP BY HAVING 支持的字段类型： Text、Boolean、Email、PhoneNumber、Number、Enum、Date、Lookup（单值	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 EXISTS(Subquery) ALL(Subquery) ANY(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 WHERE Websites.id = access_log.site_id AND count > 200);
UNION	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 支持的字段类型： COUNT 所有 COUNT(DISTINCT) 支持 GROUP BY 的类型 MAX, MIN id、Date、Datetime、Lookup、Number SUM, AVG Number	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	引用的字段类型对应的数据类型

关键字	语法	示例
SELECT DISTINCT	SELECT DISTINCT expression [, ...]	SELECT DISTINCT _name FROM _user;
UNION	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;
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;

OQL应用指南|飞书低代码平台

先进团队，先用飞书