Thread
-
Getting json-value as varchar
Andreas Joseph Krogh <andreas@visena.com> — 2022-01-06T12:28:26Z
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? -- 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> <https://www.visena.com> -
Re: Getting json-value as varchar
Thomas Markus <t.markus@proventis.net> — 2022-01-06T12:31:19Z
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 > andreas@visena.com > www.visena.com <https://www.visena.com> > <https://www.visena.com> -
Re: Getting json-value as varchar
Andreas Joseph Krogh <andreas@visena.com> — 2022-01-06T12:36:28Z
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> <https://www.visena.com> -
Re: Getting json-value as varchar
David G. Johnston <david.g.johnston@gmail.com> — 2022-01-06T13:13:40Z
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
Andreas Joseph Krogh <andreas@visena.com> — 2022-01-06T13:20:23Z
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> <https://www.visena.com> -
Re: Getting json-value as varchar
Thomas Markus <t.markus@proventis.net> — 2022-01-06T13:25:16Z
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 > andreas@visena.com > www.visena.com <https://www.visena.com> > <https://www.visena.com> -
Re: Getting json-value as varchar
David G. Johnston <david.g.johnston@gmail.com> — 2022-01-06T13:29:12Z
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
Andreas Joseph Krogh <andreas@visena.com> — 2022-01-06T13:33:31Z
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> <https://www.visena.com>
-
Re: Getting json-value as varchar
Pavel Stehule <pavel.stehule@gmail.com> — 2022-01-06T13:42:21Z
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 > andreas@visena.com > www.visena.com > <https://www.visena.com> > > -
Re: Getting json-value as varchar
Andreas Joseph Krogh <andreas@visena.com> — 2022-01-06T13:48:09Z
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> <https://www.visena.com> -
Re: Getting json-value as varchar
shammat@gmx.net — 2022-01-06T14:39:30Z
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.