0

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

Shay Zambrovski
  • 401
  • 5
  • 21
  • Why would you expect that to work? The third argument to jsonb_path_query_first needs to be of type jsonb, which '$.phone' clearly isn't. And even if it were, what the heck would you expect it to do? – jjanes Feb 28 '23 at 21:16

1 Answers1

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;