How to sum the total length of an array of uuids column-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.