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?

You link to the docs for 9.5. Is that the (quite obsolete) version you are using? — jjanes, Feb 27 '23 at 15:54
OK. Your string looks like a JSONPATH query, so now look in that doc at jsonb_path_query and it siblings. — jjanes, Feb 27 '23 at 16:29
@jjanes how do I check that jsonb_path_query returns null (path not exists)? — Shay Zambrovski, Feb 27 '23 at 16:32

score 1 · Accepted Answer · answered Feb 27 '23 at 16:41

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;

PostgreSQL: JSONB; get the key name by path

1 Answers1