On: An early look at Postgres 14: Performance and moni...

[megous]

Not much different from some_jsonb#>>'{some,path}' and once you add the need to convert out of jsonb to text, you'll not be saving any characters either. At least for queries.

For updates, it looks nice I guess.

[zdragnar → megous]

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 → zdragnar]

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 → megous]

> 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 → derefr]

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 → megous]

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 → derefr]

Oh my. :) There's always some quirky little thing to learn about PostgreSQL, lol.

[megous → megous]

Downvoters could instead provide a way to get a decoded text of a property with this new syntax, like it's possible with #>>. That would be more useful.

[derefr → megous]

Wouldn't one big difference be that with this syntax, you can use bind-parameters / joined row-tuple fields / expression values as jsonpath keys?

ETA: no, actually, I was wrong — #>> takes text[], so you can already pass it an ARRAY[] literal containing expressions. It's just all the examples in the PG docs that use the IO syntax to represent the path, and then rely on an implicit cast to text[].

[da39a3ee → megous]

Would you be able to give a bit of context for the limitations of the new syntax that you’re pointing out? Could they be overcome (and if so why did it ship like this) or are they inevitable?

[megous → da39a3ee]

I don't think it's a limitation, it's just by design. a['b'] is equivalent to a->'b' not to a->>'b', otherwise deep references (a['b']['c']) would not work because first a['b'] would return text and not jsonb value.