The JSONB object stores a massive amount of textual data in the form of key-value pairs same as the JSON objects do. We can get all the keys from the given JSONB object using the jsonb_object_keys() function in Postgres. The jsonb_object_keys() function gives all the keys present in the provided JSONB object. The following articles explain the working of the jsonb_object_keys() function in Postgres with examples, so let’s start learning.

How Does the jsonb_object_keys() Function Work in PostgreSQL?

The jsonb_object_keys() function takes the top-level JSONB object as input and returns all the keys that are present on that JSONB object. The basic syntax for the jsonb_object_keys() function is given as

jsonb_object_keys(jsonb_obj jsonb)

In the above syntax:

● The jsonb_object_keys() function takes in a JSONB object.

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

The jsonb_object_keys() function offers the same functionality as the json_object_keys() function offers for JSON data type.

We will have some practical examples to understand the concept clearly.

Example 1: Understanding the jsonb_object_keys() Function

Consider the following query to see how the jsonb_object_keys() function works.

SELECT jsonb_object_keys('{"store_name": "Bakery", 
  "street_no": 24,
  "menu_items": ["Cake", "Bread", "patties"]}');

So in this query, we have provided a JSONB object to the jsonb_object_keys() function having 3 key-value pairs. The jsonb_object_keys() function will return all the keys present in the provided JSONB object.

In the output, we can notice that all the keys are returned. The same outcome can be gained by using the * after the SELECT statement as well.

SELECT * FROM jsonb_object_keys('{"store_name": "Bakery", 
  "street_no": 24,
  "menu_items": ["Cake", "Bread", "patties"]}');

The output of this query is:

This is basically how the jsonb_object_keys() function works.

Example 2: Using the jsonb_object_keys() Function on Table Data

The function requires a JSONB object to return its keys. We can implement the jsonb_object_keys() function on the table’s column containing the JSONB objects. Moreover, this can be implemented on the table having a JSON column by first type-casting it into the JSONB data type. Consider the “online_store” table having data about the products in the shopping cart. The table looks like the following.

Now we will implement the query to get all the keys from the “products_in_cart” column. We can notice that the objects given in the “products_in_cart” column are JSON objects, so we need to typecast them into JSONB objects to implement the jsonb_object_keys() function. The whole query can be written as

SELECT jsonb_object_keys(products_in_cart::JSONB) 
FROM online_store;

We have typecasted the “products_in_cart” column to the JSONB data type and then implemented the jsonb_object_keys() function on it. We will get the keys successfully like this.

Here we notice that the query has given all the keys present in the column on which the function was implemented. This is how we can get the JSONB keys from the Postgres table containing a JSON/JSONB object column.

Alternative Method to jsonb_each() Function

If we want to get the key column of the given JSONB object, we can also make use of the jsonb_each() or jsonb_each_text() functions. These functions return the key and values from the provided JSONB object. So let’s implement the jsonb_each() function to get the key column from the same JSONB object as above. The query can be tailored as

SELECT key FROM jsonb_each('{"store_name": "Bakery", 
  "street_no": 24,
  "menu_items": ["Cake", "Bread", "patties"]}');

As the jsonb_each() function gives the columns for keys and values, in this query, we are just getting the keys by specifying the “key” after SELECT. The output looks like this:

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

Conclusion

We can get the keys from the JSONB object, by making use of the jsonb_object_keys() function. The JSONB object is provided to the jsonb_object_keys() function as an argument and the jsonb_object_keys() function will give a column containing all the keys present in that particular JSONB object. The article explained the working of the jsonb_object_keys() function with examples and different use cases.