2

I have a json[] array (_result_group) in PostgreSQL 9.4, and I want to remove its last json element (_current). I prepared with:

_result_group := (SELECT array_append(_result_group,_current));

And tried to remove with:

SELECT _result_group[1:array_length(_result_group,1) -1] INTO _result_group;

But it didn't work.
How to do this?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • So this is plpgsql code. `_result_group` is defined as `json[]` and `_current` as `json`? Initialized how exactly? Can you provide a complete, failing plpgsql block? And what exactly happens when it *does not work*? – Erwin Brandstetter Oct 22 '19 at 23:47

1 Answers1

0

To remove the last element from any array (including json[]) with the means of Postgres 9.4, obviously within plpgsql code:

_result_group := _result_group[1:cardinality(_result_group)-1];

Assuming a 1-dimensional array with default subscripts starting with 1.

You get an empty array for empty array input and null for null.

According to the manual, cardinality() ...

returns the total number of elements in the array, or 0 if the array is empty

Then just take the array slice from 1 to cardinality -1.

Then again, your attempt should work as well:

SELECT _result_group[1:array_length(_result_group,1) -1] INTO _result_group;

For non-standard array subscripts see:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Apparently the problem was a conditional, I finished using `SELECT _result_group[1:array_length(_result_group,1) -1] INTO _result_group;` and it worked –  Oct 23 '19 at 20:33