Menu
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
959
Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
PostgreSQL provides two native operators -> and ->> to help you query JSON data. The operator -> returns JSON object field as JSON. The operator ->> returns JSON object field as text.
You can save any valid json value to either json or to a jsonb column. But you cannot bind it as string/ text / varchar , if you use prepared statements (use casts instead in sql, like UPDATE ... SET json_col = $1::json or bind it as unknown ).
String data can be easily converted to JSON using the stringify() function, and also it can be done using eval() , which accepts the JavaScript expression that you will learn about in this guide.
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.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
