sql - Postgres如何将json数组解压缩为一列然后重新打包，但保留数据类型？

以下是一些数据：

create table t
(id numeric, ja json, cr timestamp with time zone);

insert into t
values
(1,'[1,2,3]','2019-01-01 00:00:00+0'),
(1,'[3,4,5]','2019-02-01 00:00:00+0'),
(2,'["a","b"]','2019-01-01 00:00:00+0');

下面是一个查询，它将解压json数组，在给定时间内只保留最新的数组元素（1月“3”将被丢弃，而2月“3”）然后重新打包数据：

  SELECT r.id, json_agg(r.v), r.cr
  FROM
  (
    SELECT
      r.id,
      j.v,
      t.cr,
      ROW_NUMBER() OVER(PARTITION BY t.id, j.v ORDER BY t.cr DESC) as rn
   FROM
     t
     CROSS JOIN LATERAL json_array_elements_text(t.ja) j(v)
  ) r
  WHERE r.rn = 1
  GROUP BY r.id, r.cr

该过程的一部分涉及将json值转换为文本，以便postgres可以在window函数中对它们进行分组，这意味着当重新打包为数组时，数字已变成文本：

id v              cr
1  ["1","2"]      2019-01-01 00:00:00+0
1  ["3","4","5"]  2019-02-01 00:00:00+0
2  ["a","b"]      2019-01-01 00:00:00+0

有没有一种方法可以将看起来像数字的字符串转换回数字，作为将它们打包回json数组的过程的一部分？

最佳答案

demo:db<>fiddle
使用json_array_elements_text()时，您正在将元素转换为文本。只需使用json_array_elements()来保留数据类型。对于窗口函数，可以将其分别转换为text。

 SELECT r.id, json_agg(r.v), r.cr
  FROM
  (
    SELECT
      t.id,
      j.v,
      t.cr,
      ROW_NUMBER() OVER(PARTITION BY t.id, j.v::text ORDER BY t.cr DESC) as rn
   FROM
     t
     CROSS JOIN LATERAL json_array_elements(t.ja) j(v)
  ) r
  WHERE r.rn = 1
  GROUP BY r.id, r.cr

关于sql - Postgres如何将json数组解压缩为一列然后重新打包，但保留数据类型？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57409694/