如何在PostgreSQL中实现jsonb列按指定字段的ans值排序？

当然可以实现！针对你这个需求，我们可以利用PostgreSQL强大的jsonb处理能力来完成，下面我会给出具体的解决方案和示例。

首先先确认你的表结构和测试数据（方便后续验证）：

CREATE TABLE foo(response jsonb); 
INSERT INTO foo VALUES 
  ('[{"qs":"field1", "ans":"a"},{"qs":"field2", "ans":"1"}]' :: jsonb), 
  ('[{"qs": "field1", "ans": "d"},{"qs": "field2", "ans": "4"}]' :: jsonb), 
  ('[{"qs": "field1", "ans": "b"},{"qs": "field2", "ans": "3"}]' :: jsonb), 
  ('[{"qs": "field1", "ans": "e"},{"qs": "field2", "ans": "2"}]' :: jsonb);

1. 按field1的ans值排序 #

我们可以使用jsonb_path_query_first函数直接从jsonb数组中提取field1对应的ans值，以此作为排序依据。如果需要同时展示提取后的字段和原始json数据，SQL如下：

SELECT
  -- 提取field1的ans值
  jsonb_path_query_first(response, '$[*] ? (@.qs == "field1")') ->> 'ans' AS field1,
  -- 提取field2的ans值
  jsonb_path_query_first(response, '$[*] ? (@.qs == "field2")') ->> 'ans' AS field2,
  -- 原始jsonb数据
  response
FROM foo
ORDER BY field1;

执行后得到的结果如下：

如果只需要输出原始的response列，SQL可以简化为：

SELECT response
FROM foo
ORDER BY jsonb_path_query_first(response, '$[*] ? (@.qs == "field1")') ->> 'ans';

2. 按field2的ans值排序 #

注意field2的ans是数字类型，排序时需要将其转换为整数（避免字符串排序的问题，比如"10"会排在"2"前面），SQL如下：

SELECT
  jsonb_path_query_first(response, '$[*] ? (@.qs == "field1")') ->> 'ans' AS field1,
  jsonb_path_query_first(response, '$[*] ? (@.qs == "field2")') ->> 'ans' AS field2,
  response
FROM foo
ORDER BY (field2)::int;

执行后得到的结果如下：

同样，如果只需要输出原始response列，简化后的SQL：

SELECT response
FROM foo
ORDER BY (jsonb_path_query_first(response, '$[*] ? (@.qs == "field2")') ->> 'ans')::int;

补充说明 #

jsonb_path_query_first函数的作用是在jsonb数组中找到第一个匹配指定条件的元素，这里我们用$[*] ? (@.qs == "field1")来筛选qs等于field1的元素，然后提取其ans值。因为你的数据中每个qs值都是唯一的，所以这个函数可以准确提取到我们需要的值。

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