Extract all of the keys, including nested, from postgres JSONB column

Question

I have a table, my_table, in postgres with a column, d, that is of a jsonb type. An example of the data in that column is:

{
"logo": "a",
"name": "b",
"status": "c",
"address": {
 "city": "d",
 "state": "e",
 "street1": "f",
 "street2": "",
 "postal_code": "g",
 "country_code": "h"
},
"industry": "i",
"logo_url": "j",
"followers": 2,
"twitter_id": "",
"description": "k",
"status_code": "l",
"website_url": "m",
"headquarters": {},
"employees_count": 5,
"employees_range": "1-10",
"current_jobs_posted": 0

}

and I would like to have the sql return:

logo
name
status
address
city
state
street1
street2
postal_code
country_code
industry
logo_url
followers
twitter_id
description
status_code
website_url
headquarters
employees_count
employees_range
current_jobs_posted

The current SQL

SELECT jsonb_object_keys(data) as key_name
FROM profiles 
WHERE jsonb_typeof(data) = 'object' 
and id = 5;
returns
logo
name
status
address
industry
logo_url
followers
twitter_id
description
status_code
website_url
headquarters
employees_count
employees_range
current_jobs_posted

leaving out the keys nested under address. I need to have those broken out as well. It can either return them as rows, or if necessary, it can be flattened and returned as columns. I just need to be able to return all of the keys, including any nested keys. There may be other keys nested in other entries in the table.

Thank you

Answer 1

You can use a recursive query for that:

with recursive all_keys (ky, nested) as (
 select t.ky, 
 case jsonb_typeof(p.data -> t.ky) 
 when 'object' then p.data -> t.ky
 else null
 end
 from profiles p
 cross join jsonb_object_keys(p.data) as t(ky)
 where id = 5 
 union all
 select t.ky,
 case jsonb_typeof(ak.nested -> t.ky) 
 when 'object' then ak.nested -> t.ky
 else null
 end
 from all_keys ak
 cross join jsonb_object_keys(ak.nested) as t(ky)
 where nested is not null
)
select ky
from all_keys;

Online example: https://rextester.com/YVNF32970