How can Postgres unpack a json array into a column then re-pack it, but preserve the data type?

Question

Here is some data:

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

Here is a query that unpacks the json array, keeps only the latest array element for a given time (january "3" is dropped in favor of february "3"), then re-packs the data:

  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

Part of the process involves converting the json values to text so that postgres can group them in the window function, which means when repacked as an array the numerics have become text:

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

Is there a way to convert the numeric-looking-strings back to number as part of the process of packing them back into an json array?

Answer 1

demo:db<>fiddle

You are converting the elements to text when using json_array_elements_text(). Just use json_array_elements() which preserves the data type. For the window function you can cast it into text separately.

 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

Answer 2

Don't convert them to text, you only need that for the partition by:

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