PostgreSQL: JSONB; get the key name by path

Question

I have a requirement, on given path, I need to get the key name of JSONB, for example:

'{
  "name": "John Doe",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "Anytown",
    "state": "CA",
    "zip": "12345",
    "data": {"a": "b", "c": "d"}
  }
}'

So given $.address.street will return street (if key exists, otherwise null).

I look in the PostgreSQL doc, but didn't found anything that can give me this.

Any help please?

Answer 1

The only way I can think of doing this:

SELECT
    CASE WHEN jsonb_path_exists('{
  "name": "John Doe",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "Anytown",
    "state": "CA",
    "zip": "12345",
    "data": {"a": "b", "c": "d"}
  }
}'::jsonb, '$.address.street') then (select * from jsonb_object_keys(jsonb_extract_path('{
  "name": "John Doe",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "Anytown",
    "state": "CA",
    "zip": "12345",
    "data": {"a": "b", "c": "d"}
  }
}'::jsonb, 'address')) as t where t = 'street')
ELSE
    NULL
    END;