Re: jsonb: unwrapping text

On Wed, Oct 27, 2021 at 04:18:20PM -0700, David G. Johnston wrote:
> On Wed, Oct 27, 2021 at 11:58 AM <tomas(at)tuxteam(dot)de> wrote:
>
> >
> > I've found out that one can treat a string singleton as if it
> > were an array:
> >
> > foo=# select '"foo"'::jsonb ->> 0;
> > ?column?
> > ----------
> > foo
> > (1 row)
> >
> > which conveniently returns the right type. My question: can I rely
> > on that, or am I missing a much more obvious option?
> >
> >
> Not sure if this exact behavior is trustworthy - but you are on the right
> path. Place the value into either a json array or json object and then use
> the text versions of the accessor methods to get the json value to pass
> through the decoding routine.

Thanks a bunch :)

I know that, behind the scenes, jsonb scalars (didn't check that
for json) are actually represented as one-element arrays, but was unsure
how much this can be relied on as "official interface" :-)

This leaves us with

foo=# select jsonb_build_array('"foo"'::jsonb)->>0;
?column?
----------
foo
(1 row)

...which feels somewhat roundabout, but hey, it actually works. I'll

What also seems to work is #>> with an empty path specifier, i.e.

select '"foo"'::jsonb #>> '{}';

...but all of them feel somewhat hacky. I'll post a request with the
form linked in [1], let's see :-)

Thanks again for your assessment, cheers
- t