Postgres 13
I have a table with jsonb array and I need to revert the array. Any idea on how I can do that?
CREATE OR REPLACE FUNCTION array_reverse(anyarray) RETURNS anyarray AS $$
SELECT ARRAY(
SELECT $1[i]
FROM generate_subscripts($1,1) AS s(i)
ORDER BY i DESC
);
$$ LANGUAGE 'sql' STRICT IMMUTABLE;
create table route (
session_id serial primary key,
name varchar(20),
data jsonb
);
insert into route(name, data)
VALUES('NY-LA',
'["{\"x\":40.83382,\"y\":-75.051552}", "{\"x\":33.740055,\"y\":-118.199982}"]'
);
insert into route(name, data)
VALUES('NY-CH',
'["{\"x\":40.83382,\"y\":-75.051552}", "{\"x\":39.740055,\"y\":-90.199982}"]');
SELECT * FROM route;
What I need is to revert the column data. E.g. data->0->'x' from route where id = 1;
returns 39.740055
. I found array_reverse()
function suggested by Postgres, but do not understand how to convert jsonb into ARRAY so that I can use array_reverse(array)
function.
Thank you.
Asked by Daniel Kniaz
(101 rep)
Sep 13, 2022, 12:00 PM
Last activity: Feb 25, 2024, 08:06 PM
Last activity: Feb 25, 2024, 08:06 PM