postgresql - 在postgresql中对jsonb进行排序

Question

我在 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)

我需要根据值“field1”对键“ans”值进行排序以生成结果，

[{"qs": "field1", "ans": "a"}, {"qs": "field2", "ans": "1"}]
[{"qs": "field1", "ans": "b"}, {"qs": "field2", "ans": "3"}]
[{"qs": "field1", "ans": "d"}, {"qs": "field2", "ans": "4"}]
[{"qs": "field1", "ans": "e"}, {"qs": "field2", "ans": "2"}]


field1 | field2
  a    |    1
  b    |    3
  d    |    4
  e    |    2

也根据'field2'排序，

[{"qs": "field1", "ans": "a"}, {"qs": "field2", "ans": "1"}]
[{"qs": "field1", "ans": "e"}, {"qs": "field2", "ans": "2"}]
[{"qs": "field1", "ans": "b"}, {"qs": "field2", "ans": "3"}]
[{"qs": "field1", "ans": "d"}, {"qs": "field2", "ans": "4"}]

field1 | field2
  a    |    1
  e    |    2
  b    |    3
  d    |    4

有没有办法在 postgresql 中实现这个？？？

Answer 1

demo:db<>fiddle

---

不知道你要不要:

A) 按原样对 JSON 对象进行排序:

| response                                                     |
| :----------------------------------------------------------- |
| [{"qs": "field1", "ans": "a"}, {"qs": "field2", "ans": "1"}] |
| [{"qs": "field1", "ans": "b"}, {"qs": "field2", "ans": "3"}] |
| [{"qs": "field1", "ans": "d"}, {"qs": "field2", "ans": "4"}] |
| [{"qs": "field1", "ans": "e"}, {"qs": "field2", "ans": "2"}] |

排序json输出的查询:

SELECT
    *
FROM foo
ORDER BY response -> 0 -> 'ans'

只需查询 ORDER BY 子句中的 ans 值。如果您想按 field2 排序，您需要将 0 更改为 1(并且可能将其转换为 int 类型code>: ORDER BY (response -> 1 ->> 'ans')::int)

---

或 B) 您只想对 ans 值进行排序:

field1 | field2
:----- | -----:
a      |      1
b      |      3
d      |      4
e      |      2

排序 ans 值的查询:

SELECT 
    response -> 0 ->> 'ans' AS field1,
    (response -> 1 ->> 'ans')::int AS field2
FROM
    foo
ORDER BY 1

通过解析数组创建两列。 field1 是 JSON 数组的第一个元素，field2 是第二个元素(索引 0 和 1)。之后您可以对这些列进行正常排序。