Thread

1. how to correctly cast json value to text?

   Pavel Stehule <pavel.stehule@gmail.com> — 2021-05-03T09:15:08Z

   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.
   
   Regards
   
   Pavel
   

2. Re: how to correctly cast json value to text?

   Pavel Stehule <pavel.stehule@gmail.com> — 2021-05-03T09:23:52Z

   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
   >
   >
   >
   

3. Re: how to correctly cast json value to text?

   Marko Tiikkaja <marko@joh.to> — 2021-05-03T09:25:59Z

   On Mon, May 3, 2021 at 12:24 PM Pavel Stehule <pavel.stehule@gmail.com>
   wrote:
   
   > Is it possible to do this with built functionality?
   >
   > I miss the cast function for json scalar string value to string.
   >
   
   #>>'{}'
   
   
   .m
   

4. Re: how to correctly cast json value to text?

   Pavel Stehule <pavel.stehule@gmail.com> — 2021-05-03T09:35:34Z

   po 3. 5. 2021 v 11:26 odesílatel Marko Tiikkaja <marko@joh.to> napsal:
   
   > On Mon, May 3, 2021 at 12:24 PM Pavel Stehule <pavel.stehule@gmail.com>
   > wrote:
   >
   >> Is it possible to do this with built functionality?
   >>
   >> I miss the cast function for json scalar string value to string.
   >>
   >
   > #>>'{}'
   >
   
   It is working. Thank you. But this syntax is a little bit scary.  Maybe we
   can introduce some functions for this case. Until to pg 14 this
   functionality was not necessary, but now it can be nice to have it.
   
   DO $$
   DECLARE v jsonb;
   BEGIN
     -- hodnota musi byt validni json
     v['a'] = '"Ahoj"';
     RAISE NOTICE '%', v['a'] #>> '{}';
   END;
   $$;
   NOTICE:  Ahoj
   DO
   
   Some ideas about the name of this function?
   
   CREATE OR REPLACE FUNCTION jsonscalar_to_text(jsonb)
   RETURNS text AS $$
   SELECT $1 #>> '{}'
   $$ LANGUAGE sql;
   
   
   
   >
   > .m
   >