From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | How to convert return values from JSON Path functions to text |
Date: | 2019-11-06 20:51:24 |
Message-ID: | 83b22dc2-39ee-7a24-5c01-a2d29fa9da44@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
The new JSON path functions in Postgres 12 are really convenient, however I cannot figure out how to properly convert their return 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-11-06 21:36:28 | Re: Locked out of schema public (pg_dump lacks backup of the grant) |
Previous Message | Peter | 2019-11-06 20:49:25 | Re: Locked out of schema public (pg_dump lacks backup of the grant) |