如何在Knex中通过MySQL JSON数组字段关联Slots与Bookings表

问题描述 #

我有slots和bookings两张表，bookings表中的slots字段以JSON数组形式存储。查询bookings数据时需要关联获取对应的slots表数据，但尝试的关联代码无法生效，返回零行数据。使用的Knex代码如下：

var items = await knex.select().from('bookings').
    innerJoin('rooms', 'bookings.roomId', 'rooms.id').
    innerJoin('users', 'bookings.userId', 'users.id').
    join('slots', 'bookings.slots', 'slots.id').// this is not working
    where("date",req.query.date).orderBy("bookings.created_at","desc").
    limit(req.query.lim).offset(req.query.off).options({nestTables: true})

原因分析 #

普通join直接将bookings.slots（JSON数组）与slots.id（单个数值）做相等匹配，类型不匹配导致无法找到关联数据，因此返回零行。

解决方案 #

需要根据你使用的数据库类型，利用对应的JSON数组匹配函数来实现关联：

1. PostgreSQL 环境 #

PostgreSQL支持将JSON数组转为整数数组后，用ANY操作符匹配单个ID：

var items = await knex.select()
  .from('bookings')
  .innerJoin('rooms', 'bookings.roomId', 'rooms.id')
  .innerJoin('users', 'bookings.userId', 'users.id')
  .joinRaw('INNER JOIN slots ON slots.id = ANY(bookings.slots::int[])')
  .where("date", req.query.date)
  .orderBy("bookings.created_at", "desc")
  .limit(req.query.lim)
  .offset(req.query.off)
  .options({ nestTables: true })

如果需要将每个booking对应的多个slots聚合为数组（避免重复的booking记录），可以用array_agg：

var items = await knex.select(
    'bookings.*',
    'rooms.*',
    'users.*',
    knex.raw('array_agg(slots.*) as slots_data')
  )
  .from('bookings')
  .innerJoin('rooms', 'bookings.roomId', 'rooms.id')
  .innerJoin('users', 'bookings.userId', 'users.id')
  .joinRaw('INNER JOIN slots ON slots.id = ANY(bookings.slots::int[])')
  .where("date", req.query.date)
  .groupBy('bookings.id', 'rooms.id', 'users.id')
  .orderBy("bookings.created_at", "desc")
  .limit(req.query.lim)
  .offset(req.query.off)

2. MySQL 环境 #

MySQL使用JSON_CONTAINS函数判断ID是否存在于JSON数组中：

var items = await knex.select()
  .from('bookings')
  .innerJoin('rooms', 'bookings.roomId', 'rooms.id')
  .innerJoin('users', 'bookings.userId', 'users.id')
  .join('slots', knex.raw('JSON_CONTAINS(bookings.slots, CAST(slots.id AS JSON))'))
  .where("date", req.query.date)
  .orderBy("bookings.created_at", "desc")
  .limit(req.query.lim)
  .offset(req.query.off)
  .options({ nestTables: true })

内容的提问来源于stack exchange，提问作者salih kallai