PostgreSQL: How to Extract Key and Value from JSON Objects

Often in PostgreSQL you may want to extract keys and values from JSON objects in a table.

You can use the json_each_text() function with the following syntax to do so:

SELECT athletes.id , js.key, js.value
FROM athletes, json_each_text((athletes.player_info)::json) AS js;

This particular example will create new columns named key and value that contain the keys and values from each JSON object in the player_info column of the athletes table.

The following example shows how to use this syntax in practice.

Example: How to Extract Key and Value from JSON Objects 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,
  player_info jsonb
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, '{"Guard":"14", "Forward":"25"}');
INSERT INTO athletes VALUES (0002, '{"Guard":"17", "Forward":"8"}');
INSERT INTO athletes VALUES (0003, '{"Guard":"32", "Forward":"15"}');
INSERT INTO athletes VALUES (0004, '{"Guard":"19", "Forward":"37"}');

-- view all rows in table
SELECT * FROM athletes;

 id |           player_info            
----+----------------------------------
  1 | {"Guard": "14", "Forward": "25"}
  2 | {"Guard": "17", "Forward": "8"}
  3 | {"Guard": "32", "Forward": "15"}
  4 | {"Guard": "19", "Forward": "37"}

The player_info column contains information in a JSON format that shows the points scored by the Guard and Forward on each basketball team.

Suppose that we would like to extract the keys and values from each row in the player_info column.

We can use the following syntax to do so:

SELECT athletes.id , js.key, js.value
FROM athletes, json_each_text((athletes.player_info)::json) AS js;

 id |   key   | value 
----+---------+-------
  1 | Guard   | 14
  1 | Forward | 25
  2 | Guard   | 17
  2 | Forward | 8
  3 | Guard   | 32
  3 | Forward | 15
  4 | Guard   | 19
  4 | Forward | 37

Notice that the resulting table contains two new columns:

• The key column shows the position of the player.
• The value column shows the points scored by the player.

Notice that each value in the key and value column are also associated with the correct value from the original id column.

Feel free to use similar syntax to extract keys and values from a JSON object in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Random Value from Array
PostgreSQL: How to Find the Size of an Array
PostgreSQL: How to Check if Array is Empty