如何对PostgreSQL的JSONB类型执行正确操作并在单查询中关联查找数据

核心错误原因 #

之前关联查询返回全为null，绝大多数是两个问题导致：

1. 没有将associations的JSONB数组展开为单行的JSON对象，无法直接用数组内的字段做关联匹配
2. 从JSONB中取出的字段值默认为字符串/数值类型，未显式转换为关联表主键的同类型，导致匹配失败

正确原生SQL逻辑 #

SELECT
  -- 保留JSON数组内的原始字段
  assoc->>'role' AS role_id,
  assoc->>'shop_id' AS shop_id,
  assoc->>'admin_id' AS admin_id,
  assoc->>'manager_id' AS manager_id,
  -- 关联表返回的字段
  r.name AS role_name,
  s.shop_name,
  admin_user.username AS admin_name,
  manager_user.username AS manager_name
FROM users
-- 展开JSONB数组为独立的JSON行记录，需要保留数组为空的用户时换成LEFT JOIN LATERAL
CROSS JOIN LATERAL jsonb_array_elements(users.associations) AS assoc
-- 关联角色表，注意JSON取值后显式转INT匹配主键类型
LEFT JOIN role r ON (assoc->>'role')::INT = r.id
-- 关联店铺表
LEFT JOIN shop s ON (assoc->>'shop_id')::INT = s.id
-- 关联用户表查管理员信息，同表关联需要取别名
LEFT JOIN users admin_user ON (assoc->>'admin_id')::INT = admin_user.id
-- 关联用户表查经理信息，null值不会匹配自动返回null
LEFT JOIN users manager_user ON (assoc->>'manager_id')::INT = manager_user.id
WHERE users.id = :id

Sequelize 调用写法 #

const queryResult = await sequelize.query(`
  SELECT
    assoc->>'role' AS role_id,
    assoc->>'shop_id' AS shop_id,
    assoc->>'admin_id' AS admin_id,
    assoc->>'manager_id' AS manager_id,
    r.name AS role_name,
    s.shop_name,
    admin_user.username AS admin_name,
    manager_user.username AS manager_name
  FROM users
  CROSS JOIN LATERAL jsonb_array_elements(users.associations) AS assoc
  LEFT JOIN role r ON (assoc->>'role')::INT = r.id
  LEFT JOIN shop s ON (assoc->>'shop_id')::INT = s.id
  LEFT JOIN users admin_user ON (assoc->>'admin_id')::INT = admin_user.id
  LEFT JOIN users manager_user ON (assoc->>'manager_id')::INT = manager_user.id
  WHERE users.id = :userId
`, {
  replacements: { userId: 你要查询的用户ID },
  type: sequelize.QueryTypes.SELECT
})

注意事项 #

• 如果你的associations字段是JSON类型而非JSONB类型，将jsonb_array_elements替换为json_array_elements
• 若关联表主键为BIGINT类型，将类型转换符::INT替换为::BIGINT
• 需要保留用户表其他字段时，直接在SELECT语句中添加users.字段名即可

内容的提问来源于stack exchange，提问作者bihire boris