Postgres: How to convert a json string to text?

Question

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

Thanks.

P.S. I'm using PostgreSQL 9.3

Answer 1

In 9.4.4 using the #>> operator works for me:

select to_json('test'::text) #>> '{}'; 

To use with a table column:

select jsoncol #>> '{}' from mytable; 

Answer 2

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.