自定義涉及數組的 jsonb 鍵排序順序

我在 PostgreSQL 中有一個包含一些數據的表：

create table t2 (
   key jsonb,
   value jsonb
);

INSERT INTO t2(key, value)
VALUES
('1', '"test 1"')
,('2', '"test 2"')
,('3', '"test 3"')
,('[]', '"test 4"')
,('[1]', '"test 5"')
,('[2]', '"test 6"')
,('[3]', '"test 7"')
,('[1, 2]', '"test 8"')
,('[1, 2, 3]', '"test 9"')
,('[1, 3]', '"test 10"')
,('[1,2,4]', '"test 11"')
,('[1, 2,4]', '"test 12"')
,('[1,3,13]', '"test 13"')
,('[1, 2, 15]', '"test 15"');

我嘗試像這樣對這些行進行排序：

SELECT key FROM t2 order by key;

結果是：

[]
1
2
3
[1]
[2] <==
[3] <==
[1, 2]
[1, 3] <==
[1, 2, 3]
[1, 2, 4]
[1, 2, 4]
[1, 2, 15]
[1, 3, 13]

但我需要的是：

[]
1
2
3
[1]
[1, 2]
[1, 2, 3]
[1, 2, 4]
[1, 2, 4]
[1, 2, 15]
[1, 3] <==
[1, 3, 13]
[2] <==
[3] <==

有沒有辦法實現它？

首先，您的問題以及您的列名"key"具有誤導性。列鍵不包含任何 JSON鍵，僅包含值。否則我們可以使用該函式jsonb_object_keys(jsonb)來提取密鑰，但事實並非如此。

假設您的所有 JSON 數組都是空的或包含整數，如圖所示。標量值（非數組）也是整數。

您的基本排序順序適用於 Postgres integer（或numeric）數組。我使用這個小輔助函式將jsonb數組轉換為 Postgres int[]：

CREATE OR REPLACE FUNCTION jsonb_arr2int_arr(_js jsonb)
  RETURNS int[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT j::int FROM jsonb_array_elements_text(_js) j)';

解釋：

• 如何將 JSON 數組轉換為 Postgres 數組？

然後添加jsonb_typeof(jsonb)到：

SELECT key
FROM   t2
ORDER  BY key <> '[]'             -- special case for empty array
       , jsonb_typeof(key) DESC  -- 'number' before 'array'
       , CASE jsonb_typeof(key)  -- sort arrays as converted int[]
           WHEN 'array'  THEN jsonb_arr2int_arr(key)
           WHEN 'number' THEN ARRAY[key::text::int]
         END;

準確地產生所需的結果。

為什麼？

手冊jsonb解釋：

基準的btree排序jsonb很少引起人們的極大興趣，但為了完整性，它是：

Object > Array > Boolean > Number > String > Null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

具有相同對數的對象按以下順序進行比較：

key-1, value-1, key-2 ...

請注意，對象鍵是按其儲存順序進行比較的；特別是，由於較短的密鑰儲存在較長的密鑰之前，這可能會導致結果可能不直覺，例如：

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

同樣，具有相同數量元素的數組按順序進行比較：

element-1, element-2 ...

大膽強調我的。

這就是為什麼jsonb '[2]' < jsonb '[1, 2]'。

但是 Postgres 數組只是逐個元素地排序：'{2}'::int[] > '{1, 2}'- 正是你要找的。

參考問題以按 json 整數值對結果進行排序。嘗試：

select myjson from mytable order by (myjson->>'some_int')::int;

在您的情況下，它似乎是排序鍵的數組。因此，首先嘗試連接“關鍵”欄位中的值。

引用自：https://dba.stackexchange.com/questions/142067