jsonb: unwrapping text

From: <tomas(at)tuxteam(dot)de>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: jsonb: unwrapping text
Date: 2021-10-27 18:58:07
Message-ID: 20211027185807.GA31130@tuxteam.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Louise Grandjonc 2021-10-27 19:39:41 Segmentation fault in volatile c function
Previous Message SQL Padawan 2021-10-27 18:06:07 Re: Strange sequences - how to construct?