0

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.

1
  • 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.
    – user1822
    Sep 14, 2022 at 8:26

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.