Search code examples
postgresqlpostgresql-9.4jsonb

Postgresql: Get all the outermost keys of a jsonb column

Assume that we have a large table with a jsonb column having only json object in it. How to get a list of all the outermost keys in column ?

i.e. if the table is something like this 

| id | data_column                                          |
| ---| -----------------------------------------------------|
| 1  | {"key_1": "some_value", "key_2": "some_value"}       |
| 2  | {"key_3": "some_value", "key_4": "some_value"}       |
| 3  | {"key_1": "some_value", "key_4": "some_object"}      |
.....

is it possible to get a result something like this

| keys |
| -----|
| key_1|
| key_2|
| key_3|
| key_4|
Solution

  • Yes:

    SELECT jsonb_object_keys(data_column) FROM test_table;

    Or if you want to remove duplicates, order and have keys as column name:

    SELECT DISTINCT jsonb_object_keys(data_column) AS keys FROM test_table ORDER by keys;

    jsonb_object_keys() / json_object_keys() returns the outer-most keys from the json object.