postgres reverse jsonb array

Question

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. select 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.

Answer 1

anyarray means any SQL-level array. It does not cover the JSONB subtype. You will need a function specifically for that:

CREATE OR REPLACE FUNCTION array_reverse(jsonb) RETURNS jsonb AS $$
SELECT jsonb_agg(value order by ordinality desc) 
    FROM jsonb_array_elements($1) with ordinality;
$$ LANGUAGE 'sql' STRICT IMMUTABLE;