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

user1822 · Accepted Answer · 2019-01-11 15:22:30Z

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

Stack Exchange Network

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

1 Answer 1

Your Answer

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions