Getting json-value as varchar

Hi,

Am 06.01.22 um 13:28 schrieb Andreas Joseph Krogh:

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)

How to I use the subscript syntax and get the result as varchar
instead of JSONB, assuming I /know/ the JSON-field is a String?

simply cast your value
SELECT (('{"key":"value"}'::jsonb)->> 'key')::text;

best regards
Thomas

På torsdag 06. januar 2022 kl. 13:31:19, skrev Thomas Markus <
t.markus@proventis.net <mailto:t.markus@proventis.net>>: Hi,

Am 06.01.22 um 13:28 schrieb Andreas Joseph Krogh:
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)

How to I use the subscript syntax and get the result as varchar instead of
JSONB, assuming Iknow the JSON-field is a String? simply cast your value
SELECT (('{"key":"value"}'::jsonb)->> 'key')::text;

best regards
Thoma

I think you misread my message. What I want is for the subscript-version:
('{"key":"value"}'::jsonb)['key']
to return:

┌──────────┐
│ ?column? │
├──────────┤
│ value │
└──────────┘

instead of

┌─────────┐
│ jsonb │
├─────────┤
│ "value" │
└─────────┘

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

On Thursday, January 6, 2022, Andreas Joseph Krogh <andreas@visena.com>
wrote:

I think you misread my message. What I want is for the subscript-version:

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

to return:

┌──────────┐
│ ?column? │
├──────────┤
│ value │
└──────────┘

instead of

┌─────────┐
│ jsonb │
├─────────┤
│ "value" │
└─────────

A given syntax/operator can only return one thing so what you want is
fundamentally not possible.

David J.

På torsdag 06. januar 2022 kl. 14:13:40, skrev David G. Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>: On Thursday,
January 6, 2022, Andreas Joseph Krogh <andreas@visena.com
<mailto:andreas@visena.com>> wrote:

I think you misread my message. What I want is for the subscript-version:
('{"key":"value"}'::jsonb)['key']
to return:

┌──────────┐
│ ?column? │
├──────────┤
│ value │
└──────────┘

instead of

┌─────────┐
│ jsonb │
├─────────┤
│ "value" │
└─────────

A given syntax/operator can only return one thing so what you want is
fundamentally not possible.

That's not very helpful....
Apparently I'm after a solution which either casts this to varchar or a
function that takes JSONB as argument and outputs the first field-value as
varchar.

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

Hi,

Am 06.01.22 um 13:36 schrieb Andreas Joseph Krogh:

På torsdag 06. januar 2022 kl. 13:31:19, skrev Thomas Markus
<t.markus@proventis.net>:

Hi,
Am 06.01.22 um 13:28 schrieb Andreas Joseph Krogh:

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)

How to I use the subscript syntax and get the result as varchar
instead of JSONB, assuming I /know/ the JSON-field is a String?

simply cast your value
SELECT (('{"key":"value"}'::jsonb)->> 'key')::text;

best regards
Thoma

I think you misread my message. What I want is for the subscript-version:

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

to return:
┌──────────┐
│ ?column? │
├──────────┤
│ value    │
└──────────┘

instead of
┌─────────┐
│  jsonb  │
├─────────┤
│ "value" │
└─────────┘

yeah right :(

complicated but this should do:
SELECT  jsonb_build_array( ('{"key":"value"}'::jsonb)['key'] ) ->> 0;

On Thursday, January 6, 2022, Andreas Joseph Krogh <andreas@visena.com>
wrote:

Apparently I'm after a solution which either casts this to varchar or a
function that takes JSONB as argument and outputs the first field-value as
varchar.

You can try casting the value though IIRC it doesn’t actually work
(limitation of the feature). The fact is that the ‘ - - > ‘ operator gives
you the needed output.

David J.

På torsdag 06. januar 2022 kl. 14:29:12, skrev David G. Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>:
[..]
The fact is that the ‘ - - > ‘ operator gives you the needed output.

David J.

Yeah, I think that's the correct answer for this use-case.

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

Hi

čt 6. 1. 2022 v 14:33 odesílatel Andreas Joseph Krogh <andreas@visena.com>
napsal:

På torsdag 06. januar 2022 kl. 14:29:12, skrev David G. Johnston <
david.g.johnston@gmail.com>:

[..]
The fact is that the ‘ - - > ‘ operator gives you the needed output.

David J.

Yeah, I think that's the correct answer for this use-case.

It is true that some other casting function is missing. I am not sure if
this is part of ANSI/SQL json support.

Now, you can use helper function

CREATE OR REPLACE FUNCTION public.to_text(jsonb)
RETURNS text
LANGUAGE sql
AS $function$
select jsonb_array_element_text($1, 0)
$function$

postgres=# select to_text((jsonb '{"a":"Ahoj"}')['a']);
┌─────────┐
│ to_text │
╞═════════╡
│ Ahoj │
└─────────┘
(1 row)

Regards

Pavel

På torsdag 06. januar 2022 kl. 14:42:21, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>:

Hi

čt 6. 1. 2022 v 14:33 odesílatel Andreas Joseph Krogh <andreas@visena.com
<mailto:andreas@visena.com>> napsal:
På torsdag 06. januar 2022 kl. 14:29:12, skrev David G. Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>:
[..]
The fact is that the ‘ - - > ‘ operator gives you the needed output.

David J.

Yeah, I think that's the correct answer for this use-case.

It is true that some other casting function is missing. I am not sure if this
is part of ANSI/SQL json support.

Now, you can use helper function

CREATE OR REPLACE FUNCTION public.to_text(jsonb)
RETURNS text
LANGUAGE sql
AS $function$
select jsonb_array_element_text($1, 0)
$function$

postgres=# select to_text((jsonb '{"a":"Ahoj"}')['a']);
┌─────────┐
│ to_text │
╞═════════╡
│ Ahoj │
└─────────┘
(1 row)

Regards

Pavel

Thanks!

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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.