undefined | Better HN

0 pointsderefr4y ago0 comments

> 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[]);

0 comments

megous4y 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;

derefrOP4y 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)

megous4y ago

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

j / k navigate · click thread line to collapse

0 pointsderefr4y ago0 comments

> 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[]);

0 comments

megous4y 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;

derefrOP4y 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)

megous4y ago

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

j / k navigate · click thread line to collapse