CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.5
iCodeByte
iCodeByte

Reputation: 681

Sum of string integers in array - Postgres

In postgres DB, I have a jsonb column which has a data in string array, like:

{
 "name": "john doe",
 "testData": "["1", "2", "3", "", "", ""]"
}

I want to convert string testData to array and then those string integers to int and then sum them up.

select sum(ARRAY(test_column->>'testData')::int) as sum from table;

Output -> "6"

Upvotes: 0

Views: 375

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you have properly formatted JSON, then you can extract the array and sum the non-empty values:

select *
from t cross join lateral
     (select sum(nullif(el, '')::int)
      from jsonb_array_elements_text(t.x->'testData') el
     ) s;

Here is a db<>fiddle.

Note that you describe the data structure as "a string array". But you have an extra set of quotes, so it is really "a string that looks like a string array".

Upvotes: 1

Related Questions