Thread: jsonb: unwrapping text

Hi,

I'm trying to extract text from a jsonb 'string'. Simply casting
gives me the string wrapped in quotes:

  foo=# select '"foo"'::jsonb::text;
   text
  -------
   "foo"
  (1 row)

This, of course, makes kind of sense, since it /is/ the JSON's
textual representation.

What is the canonical way to get at the string's content itself?

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?

(I try to stay compatible down to PostgreSQL 11, 9 when possible.
Conservative customers and that).

Thanks for any insights, cheers
-- tomás

On Wed, Oct 27, 2021 at 04:18:20PM -0700, David G. Johnston wrote:
> On Wed, Oct 27, 2021 at 11:58 AM <tomas@tuxteam.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