CODEWITHSUNDEEP
postgresqlpostgresql-9.4jsonb
Manquer
Manquer

Reputation: 7647

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|

Upvotes: 3

Views: 1134

Answers (1)

Samir Alajmovic
Samir Alajmovic

Reputation: 3283

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.

Upvotes: 4

Related Questions