PostgreSQL - Delete last item of JSONB array-CodePudding

I have a field that holds JSONB arrays. How can I delete the last item?

[
  {
    "name": "test1",
  },
  {
    "name": "test2",
  },
]

CodePudding user response：

You can use jsonb_column - (-1). See the documentation.

WITH data(j) AS (
    VALUES ('[
      {
        "name": "test1"
      },
      {
        "name": "test2"
      },
      {
        "name": "test3"
      }
    ]'::JSONB)
)
SELECT j, j - (-1) AS all_but_last, j - 1 AS without_second_element
FROM data;

j	all_but_last	without_second_element
[{"name":"test1"},{"name":"test2"},{"name":"test3"}]	[{"name":"test1"},{"name":"test2"}]	[{"name":"test1"},{"name":"test3"}]

View on DB Fiddle