如何通过对象的属性对json或jsonb值内的数组中的对象进行排序? [英] How to sort objects in an array inside a json or jsonb value by a property of the objects?

问题描述

我有这个pl/pgsql函数，可以将两个表中的行汇总为jsonb值(data_table_1和data_table_2). fk_id是两个表中的常见外键ID:

I have this pl/pgsql function to aggregate rows from two tables in a jsonb value (data_table_1 and data_table_2). fk_id is a common foreign key id in both tables:

DECLARE
v_my_variable_1 jsonb;
v_my_variable_2 jsonb;
v_combined      jsonb;
BEGIN
  SELECT json_agg( data_table_1 ) INTO v_my_variable FROM data_table_1 WHERE fk_id = v_id;
  SELECT json_agg( data_table_2 ) into v_my_variable_2 FROM data_table_2 WHERE fk_id = v_id;
  SELECT v_my_variable || v_my_variable_2 into v_combined;

现在，我想按字段ts排序v_combined，这两个表都共用一个时间戳列，因此要在jsonb值中的所有数组对象中共用一个键.

Now I want to sort v_combined by the field ts, a timestamp column common to both tables, and consequently a common key in all array objects in the jsonb value.

示例:

v_combined = '[{"id": 1, "type": 4, "param": 3, "ts": 12354355}
             , {"id": 1, "txt": "something", "args": 5, "ts": 12354345}]';

如何按ts的升序对v_combined中的数组元素进行排序?

How do I sort array elements in v_combined in ascending order for ts?

如果我从表中选择，我可以简单地使用:

If I were selecting from a table I could simply use:

select * into v_combined from v_combined ORDER BY v_combined->>'ts' ASC;

但是当我尝试这样做时，它说v_combined不存在.有没有一种方法可以将其存储在临时表中并在那里进行排序，或者有直接的方法可以对pl/pgsql中的json对象数组进行排序?

But when I try that, it says that v_combined does not exist. Is there a way of storing it in a temp table and sorting there, or is there a direct way to sort the array of json objects in pl/pgsql?

推荐答案

jsonb文字中对象中键的 键顺序 无关紧要-对象键是无论如何都在内部排序. (在这方面json是不同的.)请参阅:

The order of keys in an object in a jsonb literal is insignificant - object keys are sorted internally anyway. (json is different in this regard.) See:

• 自定义涉及数组的jsonb键排序顺序

jsonb(或json)文字中的数组元素的 顺序 很重要.您的要求是有意义的.您可以像这样重新排序:

The order of array elements in a jsonb (or json) literal is significant, though. Your request is meaningful. You can reorder like this:

SELECT jsonb_agg(elem)
FROM  (
   SELECT *
   FROM   jsonb_array_elements(v_combined) a(elem)
   ORDER  BY (elem->>'ts')::int  -- order by integer value of "ts"
   ) sub;

dbfiddle 此处

但是在分配数组之前进行排序会更有效:

But it would be more efficient to order the array before assigning it:

...
DECLARE
   v_combined      jsonb;
BEGIN
   SELECT INTO v_combined  jsonb_agg(elem)
   FROM  (
      SELECT ts, json_agg(data_table_1) AS j
      FROM   data_table_1
      WHERE  fk_id = v_id

      UNION ALL 
      SELECT ts, json_agg(data_table_2)
      FROM   data_table_2
      WHERE  fk_id = v_id
      ORDER  BY ts
      ) sub;
...

根据子查询的行顺序

在标准SQL中，子查询(或任何表表达式)中的 行顺序 也无关紧要.但是在Postgres中，子查询中的行顺序将转移到下一个级别.因此，这适用于简单的查询.甚至记录:

...通常可以使用已排序的子查询提供输入值.例如:

... supplying the input values from a sorted subquery will usually work. For example:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

请注意，如果外部查询级别包含其他处理(例如联接)，则此方法可能会失败，因为这可能会导致子查询的输出在计算聚合之前重新排序.

Beware that this approach can fail if the outer query level contains additional processing, such as a join, because that might cause the subquery's output to be reordered before the aggregate is computed.

如果您不能或不会依赖于此，则有一个安全的选择:将ORDER BY添加到聚合函数本身.甚至更短:

If you can't or won't rely on this, there is a safe alternative: add an ORDER BY to the aggregate function itself. That's even shorter:

SELECT INTO v_combined  jsonb_agg(elem  ORDER BY (elem->>'ts')::int)
FROM   jsonb_array_elements(v_combined) a(elem);

但是它通常慢些.

这篇关于如何通过对象的属性对json或jsonb值内的数组中的对象进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！