Introduction to the PostgreSQL jsonb_object_keys() function

Thejsonb_object_keys() function allows you to extract the keys of aJSON object into a set of text values.

Here’s the basic syntax of thejsonb_object_keys() function:

jsonb_object_keys(json_object)

In this syntax:

• json_object is the JSON object of type JSONB that you want to extract the keys.

Thejsonb_object_keys() function returns a set of text values representing the keys in thejson_object.

If thejson_object is not a JSON object, thejsonb_object_keys() function will issue an error.

If thejson_object isNULL, the function will return an empty set.

PostgreSQL jsonb_object_keys() function examples

Let’s take some examples of using thejsonb_object_keys() function.

1) Basic the jsonb_object_keys() function examples

The following example uses thejsonb_object_keys() function to extract the keys of a JSON object as a set of text values:

SELECT  jsonb_object_keys(    '{"name": "Joe", "age": 18, "city": "New York"}'  );

Output:

jsonb_object_keys------------------- age city name(3 rows)

2) Using the PostgreSQL jsonb_object_keys() function with table data

First,create a table calledperson:

CREATE TABLE person (    idSERIAL PRIMARY KEY,    info JSONB);

In theperson table, theinfo column has the type JSONB that stores various information about each person.

Second,insert rows into theperson table:

INSERT INTO person (info)VALUES    ('{"name": "John", "age": 30, "city": "New York"}'),    ('{"name": "Alice", "city": "Los Angeles"}'),    ('{"name": "Bob", "age": 35 }');

Third, get the keys of the objects in theinfo column:

SELECT jsonb_object_keys(info)FROM person;

Output:

jsonb_object_keys------------------- age city name city name age name(7 rows)

To get unique keys from all the stored JSON objects in the info column, you can use theDISTINCT operator:

SELECT DISTINCT jsonb_object_keys(info)FROM person;

Output:

jsonb_object_keys------------------- age city name(3 rows)

3) Dynamically accessing keys

The following example shows how to dynamically access values corresponding to each key retrieved usingjsonb_object_keys():

SELECT    id,    key,    info->key AS valueFROM    person,    jsonb_object_keys(info) AS key;

Output:

id | key  |     value----+------+---------------  1 | age  | 30  1 | city | "New York"  1 | name | "John"  2 | city | "Los Angeles"  2 | name | "Alice"  3 | age  | 35  3 | name | "Bob"(7 rows)

In this example, the query returns each key along with its corresponding value from theinfo column of theperson table.

Summary

• Use thejsonb_object_keys() function to extract the keys from a JSON object.

Last updated onFebruary 24, 2024

On this page

• Introduction to the PostgreSQL jsonb_object_keys() function
• PostgreSQL jsonb_object_keys() function examples
• Summary