CODEWITHSUNDEEP
postgresqljsonb
AndreDurao
AndreDurao

Reputation: 5795

How to get the SUM of values on a JSONB column

I've got a 1-dimension JSONB on postgresql like this:

SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb;

How to get the SUM of values on a JSONB column? Like the sum of 1+2+3?

PostgreSQL has the jsonb_object_keys function, but I was looking for something like "jsonb_object_values" (I know that this function does not exists)

# select jsonb_object_keys( '{"a": 1, "b": 2, "c": 3}'::jsonb );
 jsonb_object_keys 
-------------------
 a
 b
 c
(3 rows)

Upvotes: 5

Views: 2825

Answers (1)

Patrick
Patrick

Reputation: 32264

The jsonb_each_text() function expands a set of JSON objects into rows in (key, value) format. Since it returns a set of rows, you should use it as a row source. Since it returns data in the text format, you should cast it to the appropriate type before further processing.

SELECT sum(v::integer)
FROM jsonb_each_text('{"a": 1, "b": 2, "c": 3}'::jsonb) j(k,v);

Upvotes: 4

Related Questions