Date: Sun, 15 Sep 2024 09:07:53 +0000
To: pgsql-general@lists.postgresql.org
From: Dan Kortschak <dan+pgsql@kortschak.io>
Subject: Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
Message-ID: <93c6b90cd39ae5fdf49cbd698e8de0c6b6f9bf3f.camel@kortschak.io>
In-Reply-To: <48019d21fd7c669afb83161337f7e007ae619534.camel@kortschak.io>
References: <1aea530e4c96109b91fdde53a29e38beb625dc77.camel@kortschak.io> <20240723221121.51f2b3872d0ebfc36a6fa8ff@wanadoo.fr> <2f11b2ba-3182-492a-ab46-23cfa5ec913c@aklaver.com> <CAFCRh-8d-gsERdu-6W2fb0d6HLi8_bPkS7zVqm0J4ZakpvJQYw@mail.gmail.com> <735913e4c9601a2a7ad57d2253d74451c42ba899.camel@kortschak.io> <21078220628bcebca0395a52d42b558ed965bbc0.camel@kortschak.io> <5c86256a4e83b1af73da22faab54889564e93129.camel@kortschak.io> <7320B905-E950-4035-9A13-8C9447AB52EC@gmail.com> <48019d21fd7c669afb83161337f7e007ae619534.camel@kortschak.io>
Feedback-ID: 18519778:user:proton
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/93c6b90cd39ae5fdf49cbd698e8de0c6b6f9bf3f.camel%40kortschak.io>
Precedence: bulk

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=20

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=20

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