PostgreSQL json_object_keys() Function

A JSON object is comprised of key-value pairs that can be stored in Postgres. We can get the keys and values from the JSON objects using different Postgres JSON functions. A function called the json_object_keys() function is used to fetch the keys from the provided JSON objects. This tutorial will be about the json_object_keys() function and how we can get all the keys using this function.

PostgreSQL json_object_keys() Function

The json_object_keys() function takes the JSON object and returns the keys present in it. The basic structure of the json_object_keys() function looks like this:

json_object_keys(obj JSON)

In the above syntax:

● The json_object_keys() function takes in a JSON object of JSON data type.

● The json_object_keys() function returns the set of all the keys present in that particular JSON object in the TEXT format.

Let’s implement the function query to see how it works.

Example 1: Understanding the json_object_keys() Function

We will now execute the json_object_keys() function on the '{"st_name": "Peter", "S_id": 84, "Subjects": ["German", "Calculus"]}' JSON object. The query can be written as

SELECT json_object_keys('{"st_name": "Peter", 
  "S_id": 84,
  "Subjects": ["German", "Calculus"]}');

On execution of this query, we will see that the json_object_keys() function will return the column of all the keys present in the provided JSON object. The output of this query looks like this:

The above output shows that the query has returned all the keys present in the given JSON object.

The following query also returns the exact same output/result.

SELECT * FROM json_object_keys('{"st_name": "Peter", 
  "S_id": 84,
  "Subjects": ["German", "Calculus"]}');

The * says that the query will return all the columns that are the outcome of the function but the function only returns the column for the keys. So it returns that column.

We can also get the keys from the JSON object present in a table.

Example 2: Using the json_object_keys() Function On Table Column

If a table has a column containing the JSON object, we can get the keys present in it. Consider the table “online_store” containing the JSON column “products_in cart”. The table looks like this:

To get the keys from this column, we can write the following query.

SELECT json_object_keys(products_in_cart) 
 FROM online_store;

The above query will return the column containing keys present in the JSON “products_in_cart” column.

The output gave us the keys present in the JSON column. This is how we can get the JSON keys from the Postgres table containing a JSON object column.

Additional Information/Alternative Method to json_object_keys() Function

If we want to get the key column of the given JSON object, we can also make use of the json_each() or json_each_text() functions. These functions return the key and values from the provided JSON object separately. So to get only the key column from the JSON object using the json_each() function, specify the key column in the SELECT query as follows:

SELECT key FROM json_each('{"st_name": "Peter", 
  "S_id": 84,
  "Subjects": ["German", "Calculus"]}');

The output looks like this:

You can observe that the key column is returned, containing all the keys in the provided JSON object. This is how we can customize the json_each() and json_each_text() functions to get the keys from the JSON object.

Conclusion

To get all the keys from any JSON object, we use the json_object_keys() function. The JSON object is provided to the json_object_keys() function as an argument and the json_object_keys() function will give a column containing all the keys present in that particular JSON object. This article demonstrates the working of the json_object_keys() function with examples.

PostgreSQL json_object_keys() Function