3 ms·

I think the difference is familiarity. It shouldn't matter so much, but when you don't use one language as much as you do other languages, it becomes that much

I think the difference is familiarity. It shouldn't matter so much, but when you don't use one language as much as you do other languages, it becomes that much harder to remember unfamiliar syntaxes and grammars, and easier to confuse similar looking operations with each other.

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 ago
    Thanks 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 ago
      You 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 ago
        Oh my. :) There's always some quirky little thing to learn about PostgreSQL, lol.

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 ago
  Thanks 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 ago
    You 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 ago
      Oh my. :) There's always some quirky little thing to learn about PostgreSQL, lol.