PostgreSQL JSONB – How to Extract All Keys Including Nested

postgresql

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

Best Answer

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

Related Question