如何使用Knex查询存储JSON对象的列中age属性最大值对应行
Knex查询JSON字段最大值行方案
Knex没有针对JSON字段操作的专用封装方法,需要结合你使用的数据库类型,配合raw语法实现需求,不同数据库的实现写法如下:
PostgreSQL 环境
PostgreSQL使用->>运算符提取JSON字段的属性值,转成数值类型后倒序排序取首行即可:
const maxAgeRow = await knex('myTable') .select('*') .orderBy(knex.raw('(raw_json->>\'age\')::integer'), 'desc') .first()
如果raw_json是jsonb类型,上述写法依然兼容。
MySQL 环境
MySQL使用JSON_EXTRACT函数或者简化运算符->>提取JSON属性:
const maxAgeRow = await knex('myTable') .select('*') .orderBy(knex.raw('CAST(JSON_EXTRACT(raw_json, "$.age") AS UNSIGNED)'), 'desc') .first()
也可以用简化写法:
const maxAgeRow = await knex('myTable') .select('*') .orderBy(knex.raw('CAST(raw_json->>"$.age" AS UNSIGNED)'), 'desc') .first()
SQLite 环境
SQLite使用json_extract函数提取JSON属性:
const maxAgeRow = await knex('myTable') .select('*') .orderBy(knex.raw('CAST(json_extract(raw_json, "$.age") AS INTEGER)'), 'desc') .first()
注意事项:
- 如果age存储的是浮点型数值,将上面的类型转换部分对应修改为
::float(PostgreSQL)、DECIMAL(MySQL)、REAL(SQLite)即可。- 上述写法只会返回第一条匹配到的最大值行,如果有多个age相同的最大值行需要全部返回,可以先查询最大age值再做等值匹配,示例如下(适配PostgreSQL,其他数据库调整JSON提取逻辑即可):
// 先查最大age值 const maxAge = await knex('myTable') .max(knex.raw('(raw_json->>\'age\')::integer as max_age')) .first() .then(res => res.max_age) // 查所有匹配最大值的行 const allMaxAgeRows = await knex('myTable') .select('*') .where(knex.raw('(raw_json->>\'age\')::integer = ?', [maxAge]))
内容的提问来源于stack exchange,提问作者Sequinex
相关产品推荐
相关产品推荐

