3 ms·
> Is there even a function that would convert JSON encoded "string" to text it represents in postgresql? I didn't find it. Oddly, no, there's no specific funct
by derefr 5y ago
> Is there even a function that would convert JSON encoded "string" to text it represents in postgresql? I didn't find it.
Oddly, no, there's no specific function for taking a root-level scalar JSON term (like '"foo"'::jsonb), and extracting said scalar to its equivalent native Postgres type.
You can still do it (for extracting to text, at least), but you have to use a 'vacuous' path-navigation to accomplish it, so it's extremely clumsy, and wastes the potential of the new syntax:
SELECT '"foo"'::jsonb #>> (ARRAY[]::text[]);
- megous 5y agoThanks for the idea. This is a bit shorter: SELECT '"foo"'::jsonb #>>'{}'; But yeah: SELECT jsonb_col['prop1']#>>'{}' FROM ...; looks a bit meh. And custom right unary operators are on the way out, so one can't even create one for this use case. Anyway, for fun: create function deref_jsonb(jsonb) returns text as $$ begin return $1#>>'{}'; end $$ language plpgsql; CREATE OPERATOR # ( leftarg = jsonb, function = deref_jsonb ); select '"sdfasdf"'::jsonb #; select jsonb_col['a']# FROM somewhere; :)
- derefr 5y agoYou could also take advantage of PG's function/field equivalence: -- equivalent to deref_jsonb('"sdfasdf"'::jsonb) select ('"sdfasdf"'::jsonb).deref_jsonb; (I'd suggest naming the function "inner_text", for familiarity to JS devs :P)
- megous 5y agoOh my. :) There's always some quirky little thing to learn about PostgreSQL, lol.