Postgres: How to convert a json string to text?
JsonPostgresqlJson Problem Overview
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
Json Solutions
Solution 1 - Json
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;
Solution 2 - Json
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.
Solution 3 - Json
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)
Solution 4 - Json
->> works for me.
postgres version:
<postgres.version>11.6</postgres.version>
Query:
select object_details->'valuationDate' as asofJson, object_details->>'valuationDate' as asofText from MyJsonbTable;
Output:
asofJson asofText
"2020-06-26" 2020-06-26
"2020-06-25" 2020-06-25
"2020-06-25" 2020-06-25
"2020-06-25" 2020-06-25
Solution 5 - Json
An easy way of doing this:
SELECT ('[' || to_json('Some "text"'::TEXT) || ']')::json ->> 0;
Just convert the json string into a json list