jsonpostgresql

How to convert a json string to text?

JSON value may consist of a string value. eg.:

postgres=# SELECT to_json('Some "text"'::TEXT);
     to_json
-----------------
 "Some \"text\""

How can I extract that string as a Postgres text value?

::TEXT doesn't work. It returns quoted json, not the original string:

postgres=# SELECT to_json('Some "text"'::TEXT)::TEXT;
     to_json
-----------------
 "Some \"text\""

P.S. I'm using PostgreSQL 9.3

Solution

  • There is no way in PostgreSQL to deconstruct a scalar JSON object. Thus, as you point out,

    select  length(to_json('Some "text"'::TEXT) ::TEXT);

    is 15,

    The trick is to convert the JSON into an array of one JSON element, then extract that element using ->>.

    select length( array_to_json(array[to_json('Some "text"'::TEXT)])->>0 );

    will return 11.