How get the value of a jsonb object as text with jsonb_path

From a json object i can get the value of the requestId as text with:

"request" ->> 'requestId'

with -> i get a json part

"request" -> 'requestId'

How does it works with the jsonb_path_query() to get the value as text?

jsonb_path_query(request :: jsonb, '$.requestId')

one solution should be:

select test1 ->> 0 
from ( 
 select jsonb_path_query(request :: jsonb, '$.journeys[*].costs.distance.value') test1,
        jsonb_path_query(request :: jsonb, '$.journeys[*].costs.distance.unit') test2 
 from json_import ri
) foo;

or is there a smarter way to get the same answer?

CodePudding user response：

Extract the string from the resulting jsonb:

SELECT j ->> 0
FROM jsonb_path_query(request::jsonb, '$.requestId') AS j(j);

CodePudding user response：

You can simplify the query by selecting the "parent" object in your JSON path expression:

select dist ->> 'value' as test1, 
       dist ->> 'cost' as test2
from ( 
  select jsonb_path_query(request::jsonb, '$.journeys[*].costs.distance') as dist
  from json_import ri
) foo;

Might even be a bit faster as the jsonb_path_query is only called once.