JSONB列:仅对存储在具有混合JSONB内容的列中的数组的内容进行排序 [英] JSONB column: sort only content of arrays stored in column with mixed JSONB content

问题描述

我有一个带有JSONB列的表，该表存储JSONB数组/字符串(在下面的示例中为value_r列).在JSONB列中仅排序JSONB数组内容(存储字符串)的最简单(有效)方法是什么?

I have a table with JSONB column storing JSONB arrays/strings (value_r column in the below example). What would be the simplest (and efficent) way to sort only content of JSONB arrays within JSONB column (storing also strings)?

我一直在寻找最简单的方法(因为需要查询还是需要过程?)，因为我必须将其应用于更复杂的SQL代码中.

I've been looking for the simplest method (as query, or procedure is needed?) because I have to apply this in more complicated SQL code.

这是测试代码:

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查询/过程，以使其一致且可比.假设这些是预期的结果:

Expected results - because I am going to compare arrays from two different tables, thus I would like to unify content of arrays, so '["AT", "AC"]' and '["AC", "AT"]' become the same. Frankly speaking, it doesn't matter which "default" sort is used: ASC or DESC - I will just have to run the same SQL query/procedure for two tables to make it consistent and comparable. Let's say that these are expected results:

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"]');

推荐答案

使用功能:

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.

这篇关于JSONB列:仅对存储在具有混合JSONB内容的列中的数组的内容进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！