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.

• user1822: Note that the elements of your arrays are simple strings due to the escaping of the ". There is no key "x" in your sample data, so data -> 0 -> 'x' won't return anything.

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;