postgresql - JSONB 列 : sort only content of arrays stored in column with mixed JSONB content

Question

我有一个表，其中 JSONB 列存储 JSONB 数组/字符串(下例中的 value_r 列)。仅对 JSONB 列中的 JSONB 数组的内容进行排序(还存储字符串)的最简单(且有效)的方法是什么？

我一直在寻找最简单的方法(因为需要查询或过程？)，因为我必须在更复杂的 SQL 代码中应用它。

测试代码如下:

CREATE TABLE test_table (
    id integer,
    ordinality bigint,
    key_r text,
    value_r jsonb
);

INSERT INTO test_table VALUES (1, 1, 'carType', '"sedan"');
INSERT INTO test_table VALUES (1, 1, 'equipment', '["AT", "AC"]');
INSERT INTO test_table VALUES (1, 2, 'extra', '["GPS"]');
INSERT INTO test_table VALUES (1, 2, 'carType', '"hatchback"');
INSERT INTO test_table VALUES (2, 1, 'carType', '"sedan"');
INSERT INTO test_table VALUES (2, 1, 'equipment', '["BB", "AA"]');
INSERT INTO test_table VALUES (3, 1, 'carType', '"hatchback"');
INSERT INTO test_table VALUES (3, 1, 'equipment', '["AT"]');

编辑:

预期结果 - 因为我要比较来自两个不同表的数组，所以我想统一数组的内容，所以 '["AT", "AC"]' 和 '["AC", "AT"]' 变得相同。坦率地说，使用哪种“默认”排序并不重要:ASC 或 DESC - 我只需要对两个表运行相同的 SQL 查询/过程以使其一致和可比。假设这些是预期结果:

INSERT INTO test_table VALUES (1, 1, 'carType', '"sedan"');
INSERT INTO test_table VALUES (1, 1, 'equipment', '["AC", "AT"]'); -- change here
INSERT INTO test_table VALUES (1, 2, 'extra', '["GPS"]');
INSERT INTO test_table VALUES (1, 2, 'carType', '"hatchback"');
INSERT INTO test_table VALUES (2, 1, 'carType', '"sedan"');
INSERT INTO test_table VALUES (2, 1, 'equipment', '["AA", "BB"]');  -- change here
INSERT INTO test_table VALUES (3, 1, 'carType', '"hatchback"');
INSERT INTO test_table VALUES (3, 1, 'equipment', '["AT"]');

Answer 1

使用函数:

create or replace function jsonb_sort_array(jsonb)
returns jsonb language sql immutable as $$
    select jsonb_agg(elem order by elem)
    from jsonb_array_elements($1) elem
$$;

select *, 
    case jsonb_typeof(value_r)
    when 'array' then jsonb_sort_array(value_r)
    else value_r
    end as sorted_value
from test_table;

 id | ordinality |   key_r   |   value_r    | sorted_value 
----+------------+-----------+--------------+--------------
  1 |          1 | carType   | "sedan"      | "sedan"
  1 |          1 | equipment | ["AT", "AC"] | ["AC", "AT"]
  1 |          2 | extra     | ["GPS"]      | ["GPS"]
  1 |          2 | carType   | "hatchback"  | "hatchback"
  2 |          1 | carType   | "sedan"      | "sedan"
  2 |          1 | equipment | ["BB", "AA"] | ["AA", "BB"]
  3 |          1 | carType   | "hatchback"  | "hatchback"
  3 |          1 | equipment | ["AT"]       | ["AT"]
(8 rows)    

DbFiddle.