Sum of string integers in array - Postgres

Question

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"

Gordon Linoff · Accepted Answer · 2021-06-16 19:37:07Z

1

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".

answered Jun 16, 2021 at 19:37

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iCodeByte Over a year ago

That's right @Gordon_Linoff, it is string that looks like string array. I just checked your fiddle and you might need to insert the testData in this form: "["1", "2"....]"

Collectives™ on Stack Overflow

Sum of string integers in array - Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related