Question 114294 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to sum the total length of an array of uuids column

Currently, I have 1 table consisting of id and otherIds
I want to calculate the sum of otherIds present in database.

id: 1, otherIds: {1,2,3,4,5}
id: 2, otherIds: {3,4,5}
id: 3, otherIds: {9,2,1}
id: 4, otherIds: {}

Desired result: 11 (5 + 3 + 3 + 0)

SELECT
   sum(jsonb_array_elements("table"."otherIds")) as "sumLength"
FROM
   "Table"
LIMIT 1
[42883] ERROR: function jsonb_array_elements(uuid[]) does not exist

Tags:

2

Answers


  1. 0

    I don’t see how JSONB is relevant here. If otherIds is an array of UUID values then wouldn’t you just need

    SELECT
   SUM(ARRAY_LENGTH("table"."otherIds")) as "sumLength"
FROM
   "Table"
LIMIT 1
    Login or Signup to reply.
  2. 0

    You can get the number of elements in an array with the cardinality() function. Just sum the results over all rows.

    I’d like to remark that a table design that includes an array of UUIDs is not pretty and will probable gibe you performance and data integrity problems some day.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search