Sample Header Ad - 728x90

postgres reverse jsonb array

0 votes
1 answer
1346 views
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