The new JSON path functions in Postgres 12 are really convenient, however I cannot figure out how to properly convert
theirreturn values to a text value.
E.g. select jsonb_path_query_first('{"a": "foo"}', '$.a') returns a JSONB value.
Casting it to text, still yields "foo" (with quotes), rather than foo (without quotes)
For the time being I am using something like this:
create function jsonb_to_text(p_value jsonb)
returns text
as
$$
select case jsonb_typeof(p_value)
when 'string' then trim('"' from p_value::text)
else p_value::text
end;
$$
language sql
immutable
strict;
But that feels a bit "heavyweight" - I was hoping for an easier (and more efficient) way to do that.
Thomas