Postgres: How to convert a json string to text?
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;
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.
Mr. Curious was curious about this as well. In addition to the #>> '{}' operator, in 9.6+ one can get the value of a jsonb string with the ->> operator:
select to_jsonb('Some "text"'::TEXT)->>0; ?column?------------- Some "text"(1 row)If one has a json value, then the solution is to cast into jsonb first:
select to_json('Some "text"'::TEXT)::jsonb->>0; ?column?------------- Some "text"(1 row)