e79ene e79ene - 23 days ago 16
JSON Question

Postgres: How to convert json string to text?

Json value may consist of a sting value. E.g.

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


How can I extract that string as 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 usnig PostgreSQL 9.3

Answer

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.

Comments