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

User · Answer

In 9 4 4 using the   gt  gt  operator works for me   select to json  test   text    gt  gt          To use with a table column   select jsoncol   gt  gt       from mytable

User · 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 - gt  gt    select length  array to json array to json  Some  text    TEXT   - gt  gt 0      will return 11

User · Answer

- gt  gt  works for me  postgres version   lt postgres version gt 11 6 lt  postgres version gt   Query  select object details- gt  valuationDate  as asofJson  object details- gt  gt  valuationDate  as asofText from MyJsonbTable   Output    asofJson       asofText  quot 2020-06-26 quot     2020-06-26  quot 2020-06-25 quot     2020-06-25  quot 2020-06-25 quot     2020-06-25  quot 2020-06-25 quot     2020-06-25

User · Answer

An easy way of doing this   SELECT          to json  Some  text    TEXT           json - gt  gt  0    Just convert the json string into a json list

User · Answer

Mr  Curious was curious about this as well  In addition to the   gt  gt       operator  in 9 6  one can get the value of a jsonb string with the - gt  gt  operator   select to jsonb  Some  text    TEXT - gt  gt 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- gt  gt 0     column  -------------  Some  text   1 row

[json] Postgres: How to convert a json string to text?

Examples related to json

Examples related to postgresql