Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres

[Dan Kortschak <dan+pgsql@xxxxxxxxxxxx>]

I have come to hopefully my last stumbling point.

I am unable to see a way to express something like this SQLite syntax

select json_group_array(json_replace(value,
  '$.a', case
    when json_extract(value, '$.a') > 2 then
      2
    else
      json_extract(value, '$.a')
    end,
  '$.b', case
    when json_extract(value, '$.b') < -2 then
      -2
    else
      json_extract(value, '$.b')
    end
))
from
  json_each('[{"a":1, "b":-3},{"a":2, "b":-2},{"a":3, "b":-1}]');

(in the repro above, the values are integers, but in the real case,
they are timestamps)

I have worked on multiple statements around the theme of 

with t as (
  select jsonb($$[{"a":1, "b":-3},{"a":2, "b":-2},{"a":3, "b":-1}]$$)
arr
)
select
  jsonb_array_elements(arr) as arr
from
  t;

The closest that I have come is 

with t as (
  select jsonb($$[{"a":1, "b":-3},{"a":2, "b":-2},{"a":3, "b":-1}]$$)
arr
)
select jsonb_set(arr, '{a}', case
  when (arr->>'a')::INTEGER > 2 then
    2
  else
    (arr->>'a')::INTEGER
  end
)
from (
  select
    jsonb_array_elements(arr) as arr
  from
    t
) elements;

but this is a millions miles from where I want to be (it doesn't work,
but I think the shape of the things that it's working with are maybe
heading in the right direction). I've read through the docs, but I just
don't seem able to get my head around this.

Any help would be greatful appreciated (also some reading direction so
that I'm not floundering so much).

thanks