Postgres Pro
Downloads
Home   >   mailing lists

Thread: Getting json-value as varchar

Getting json-value as varchar

From
Andreas Joseph Krogh
Date:
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?
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment

Re: Getting json-value as varchar

From
Thomas Markus
Date:
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
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963

Attachment

Re: Getting json-value as varchar

From
Andreas Joseph Krogh
Date:
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" │
└─────────┘
 
 
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: Getting json-value as varchar

From
"David G. Johnston"
Date:
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.

Re: Getting json-value as varchar

From
Andreas Joseph Krogh
Date:
På torsdag 06. januar 2022 kl. 14:13:40, skrev David G. Johnston <david.g.johnston@gmail.com>:
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.
 
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
 
Attachment

Re: Getting json-value as varchar

From
Thomas Markus
Date:
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;



 
 
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 

Attachment

Re: Getting json-value as varchar

From
"David G. Johnston"
Date:
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.

Re: Getting json-value as varchar

From
Andreas Joseph Krogh
Date:
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.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: Getting json-value as varchar

From
Pavel Stehule
Date:
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

 
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: Getting json-value as varchar

From
Andreas Joseph Krogh
Date:
På torsdag 06. januar 2022 kl. 14:42:21, skrev Pavel Stehule <pavel.stehule@gmail.com>:
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
 
Thanks!
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: Getting json-value as varchar

From
Thomas Kellerer
Date:
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.