4 ms·
In that case this does not help. SELECT json['a']; will not return the value of the string in {"a":"ble"} (like it does in Javascript), but a JSON encoding of t
by megous 5y ago
In that case this does not help. SELECT json['a']; will not return the value of the string in {"a":"ble"} (like it does in Javascript), but a JSON encoding of that string, so '"ble"'. You'll still not be able to do simple comparisons like `SELECT json_col['a'] = some_text_col;` Superficial familiarity, but it still behaves differently than you expect.
Is there even a function that would convert JSON encoded "string" to text it represents in postgresql? I didn't find it.
So all you can do is `SELECT json_col['a'] = some_text_col::jsonb;` and hope for the best (that string encodings will match) or use the old syntax with ->> or #>>.
- 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.