PostgreSQL如何基于关联特征表的jsonb字段使用ORDER BY查询学生记录

解决方案 #

你当前使用的是PostgreSQL数据库（支持jsonb类型），可使用以下SQL实现需求：

SELECT 
  s.first_name,
  s.age,
  c.value AS country
FROM students s
-- 拆分jsonb数组为单个特征ID
INNER JOIN jsonb_array_elements_text(s.character_values) AS t(character_id)
  ON TRUE
-- 关联特征表匹配国家维度的记录
INNER JOIN characteristics c
  ON c.id = t.character_id::INTEGER
  AND c.name = 'country'
-- 按国家名称升序排序
ORDER BY c.value ASC;

逻辑说明 #

• jsonb_array_elements_text函数可以将jsonb数组类型的字段拆分为多行，每行对应数组中的一个元素，这里用来把每个学生的所有特征ID拆成单独行做关联
• 因为题目明确每个学生仅属于一个国家，关联后每个学生只会对应一行国家记录，不会出现数据冗余
• 如果需要查询学生的所有原始字段，把SELECT后的字段改为s.*, c.value AS country即可

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