Re: how to correctly cast json value to text? - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: how to correctly cast json value to text?
Date
Msg-id CAFj8pRBrwzBaz_qJA7Kqh-v2J0Wde1uXOyGz8an6ERt-pfd1LQ@mail.gmail.com
Whole thread Raw
In response to how to correctly cast json value to text?  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: how to correctly cast json value to text?
List pgsql-hackers
Hi

po 3. 5. 2021 v 11:15 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
Hi

I am testing a new subscripting interface for jsonb, and I found one issue.

DO $$
DECLARE v jsonb;
BEGIN
  v['a'] = '"Ahoj"';
  RAISE NOTICE '%', v['a'];
END;
$$;
NOTICE:  "Ahoj"
DO

When I use this interface for reading, the jsonb type is returned. What is the correct way for casting from jsonb text to text value? I would not double quotes inside the result. Cast to text doesn't help. For operator API we can use "->>" symbol. But we have nothing similar for subscript API.

now I need function like

CREATE OR REPLACE FUNCTION public.value_to_text(jsonb)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
DECLARE x jsonb;
BEGIN
  x['x'] = $1;
  RETURN x->>'x';
END;
$function$

DO $$            
DECLARE v jsonb;
BEGIN
  -- hodnota musi byt validni json
  v['a'] = '"Ahoj"';
  RAISE NOTICE '%', value_to_text(v['a']);
END;
$$;
NOTICE:  Ahoj
DO

Is it possible to do this with built functionality?

I miss the cast function for json scalar string value to string.

Regards

Pavel


Regards

Pavel


pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: how to correctly cast json value to text?
Next
From: Marko Tiikkaja
Date:
Subject: Re: how to correctly cast json value to text?