Re: Getting json-value as varchar

Andreas Joseph Krogh schrieb am 06.01.2022 um 13:28:
> Hi, in PG-14 this query returns "value" (with double-quotes):
> SELECT ('{"key":"value"}'::jsonb)['key'];
> ┌─────────┐
> │  jsonb  │
> ├─────────┤
> │ "value" │
> └─────────┘
> (1 row)
>
> and this returns 'value' (without the quotes):
> SELECT ('{"key":"value"}'::jsonb)->> 'key';
> ┌──────────┐
> │ ?column? │
> ├──────────┤
> │ value    │
> └──────────┘
> (1 row)
>

Unfortunately there isn't a direct cast from jsonb to text, but the #>> operator can be (mis)used for this:

SELECT ('{"key":"value"}'::jsonb)['key'] #>> '{}'

you can put that into a function if you need that frequently.