Often in PostgreSQL you may want to cast a key in a JSONB column to an integer.
You can use the following syntax to do so:
SELECT team,
SUM((player_info->>'points')::int)
FROM athletes
GROUP BY team;
This particular example will calculate the sum of values in the key named points of the JSONB field named player_info, grouped by the team column.
By using ::int we are able to cast the points key to an integer so that we don’t receive any error when using the SUM function to sum the values.
The following example shows how to use this syntax in practice.
Example: How to Cast JSONB to Integer in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball teams:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER,
team TEXT,
player_info jsonb
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', '{"assists":"4", "points":"25"}');
INSERT INTO athletes VALUES (0002, 'Mavs', '{"assists":"7", "points":"8"}');
INSERT INTO athletes VALUES (0003, 'Mavs', '{"assists":"5", "points":"15"}');
INSERT INTO athletes VALUES (0004, 'Celtics', '{"assists":"5", "points":"30"}');
INSERT INTO athletes VALUES (0005, 'Celtics', '{"assists":"11", "points":"18"}');
INSERT INTO athletes VALUES (0006, 'Celtics', '{"assists":"2", "points":"24"}');
-- view all rows in table
SELECT * FROM athletes;
id | team | player_info
----+---------+-----------------------------------
1 | Mavs | {"points": "25", "assists": "4"}
2 | Mavs | {"points": "8", "assists": "7"}
3 | Mavs | {"points": "15", "assists": "5"}
4 | Celtics | {"points": "30", "assists": "5"}
5 | Celtics | {"points": "18", "assists": "11"}
6 | Celtics | {"points": "24", "assists": "2"}
The player_info column contains information in a JSON format that shows the total points and assists for each player.
Suppose that we would like to calculate the sum of values for the points key in the player_info column, grouped by team.
Suppose we attempt to use the following syntax to do so:
SELECT team,
SUM(player_info->>'points')
FROM athletes
GROUP BY team;
psql:commands.sql:22: ERROR: function sum(text) does not exist
LINE 2: SUM(player_info->>'points')
^
HINT: No function matches the given name and argument types.
You might need to add explicit type casts.
We receive an error that we attempted to calculate the sum of a text field.
To avoid this error, we must cast the points key from the JSONB column to an integer before attempting to calculate the sum:
SELECT team,
SUM((player_info->>'points')::int)
FROM athletes
GROUP BY team;
team | sum
---------+-----
Mavs | 48
Celtics | 72
The output correctly displays the sum of values in the points key of the player_info column, grouped by team.
Feel free to use similar syntax to cast a key from a JSONB column to an integer in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Extract Key and Value from JSON Objects
PostgreSQL: How to Query Where JSON Column Contains Value
PostgreSQL: How to Export Table to JSON File
