如何根据对象的属性对 json 或 jsonb 值内的数组中的对象进行排序？

我有这个 pl/pgsql 函数来聚合两个表中的行jsonb value (data_table_1 and data_table_2). fk_id是两个表中的公共外键 id：

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;

现在我想排序v_combined由田地ts，两个表共有的时间戳列，因此是所有数组对象中的公共键jsonb value.

Example:

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

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

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

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

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

The 对象中键的顺序 in a jsonb文字是微不足道的 - 对象键无论如何都在内部排序。 (json在这方面有所不同。）参见：

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

The 数组元素的顺序 in a jsonb (or json) 不过，字面意思很重要。你的要求很有意义。您可以像这样重新排序：

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;

数据库小提琴here

但它会是更高效对数组进行排序before分配它：

...
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 中，子查询中的行顺序被转移到下一个级别。所以这适用于简单的查询。是均匀的有记录的:

...从排序子查询提供输入值通常会起作用。例如：

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

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

如果您不能或不会依赖于此，还有一个安全的替代方案：添加ORDER BY到聚合函数本身。那就更短了：

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

但这通常是slower.