PostgreSQL | JSONB: get the first path value

Question

Given a JSON input:

{
    "name": "John Doe",
    "age": 30,
    "address": {
        "street": "123 Main St",
        "city": "Anytown",
        "state": "CA"
    },
    "phone": 1234567
}

Is it possible (if so and how? I could not managed to do so) to get the first exists path's value.

I've tried: select jsonb_path_query_first(jsonb_data, '$.address.region', '$.phone') hopefully it will returns 1234567 without any success.

Any help?

Thanks

Answer 1

You can use coalesce():

select coalesce(
         jsonb_data #>> '{address,region}', 
         jsonb_data #>> '{phone}'
       )
from the_table;

Or using a JSON path:

select coalesce(
          jsonb_path_query_first(jsonb_data, '$.address.region'), 
          jsonb_path_query_first(jsonb_data, '$.phone')
       )
from the_table;