Extracting several JSON object fields with one JSONPath without bracket notation

Question

Using PostgreSQL, I need to extract one value from JSON, which could be at several different paths. For example, consider the following JSONs:

{"a": 1, "b": 2, "c": 3}

and

{"a": 1, "b": 2, "d": 4}

I need to extract fields c or d, getting 3 and 4 respectively.

I could use a JSONPath bracket notation for union, but unfortunately, PostgreSQL's implementation uses it for arrays only.
So, the idea works on arrays:

> SELECT JSON_QUERY(jsonb '[1, 2, 3]', '$[3, 2]' ERROR ON ERROR)
3

But it doesn't work for my case:

> SELECT JSON_QUERY(jsonb '{"a": 1, "b": 2, "c": 3}', '$["a"]' ERROR ON ERROR)
SQL Error [22033]: ERROR: jsonpath array subscript is not a single numeric value

Are there any ways to perform this task with one JSONPath expression?
I'd want to use this expression in JSON_TABLE call. Of course, there is an option to evaluate each expression separately, and then use COALESCE in plain SQL, but this

1. Adds a separate transformation step
2. Requires manual checking whether only one value was found. With ERROR ON ERROR, I have PostgreSQL checking this for me

So, I'd like to avoid this workaround if possible.

Answer 1

You can use a JSONPath ? filter expression based on .keyvalue() method.
It's good to narrow things down with ?| operator to only process rows that hold a jsonb value with a c or d key, or both:
_{demo at db<>fiddle}

create table test(jbdata)as values
 ('{"a": 1, "b": 2, "c": 3}'::jsonb)
,('{"a": 1, "b": 2, "d": 4}');

select json_query(jbdata,'$.keyvalue()?(@.key=="d" || @.key=="c").value' 
                  error on error) 
from test
where jbdata ?| array['c','d'];

json_query
3
4

As a JSON_TABLE:

select json_table.*
from test
cross join lateral 
  json_table(jbdata,'$.keyvalue()?(@.key=="d" || @.key=="c")' 
             columns ( id for ordinality
                      ,k text path '$.key'
                      ,v int  path '$.value')
             error on error)
where jbdata ?| array['c','d'];

id	k	v
1	c	3
1	d	4

Note that ?| is only supported by the default jsonb_ops GIN index operator class but not the jsonb_path_ops. If you're using the latter, you need to replace ?| with @?, or @@:

where jbdata @? '$.c' or jbdata @? '$.d';
where jbdata @@ 'exists($.c) || exists($.d)';