Thread: Casting hstore to json

On 01/15/2015 07:59 PM, Pawel Veselov wrote:
> Hi.
>
> I'm trying to cast hstore to json, but I don't seem to be getting a json
> object from hstore key/value pairs.
>
> 9.3 documentation says:
> *Note:* The hstore
> <http://www.postgresql.org/docs/9.3/static/hstore.html> extension has a
> cast from hstore to json, so that converted hstore values are
> represented as JSON objects, not as string values.
> to_json(anyelement) - If the data type is not built in, and there is a
> cast from the type tojson, the cast function will be used to perform the
> conversion.
>
> On 9.3.5 server:
> db=> SELECT '"a"=>1, "b"=>2'::hstore;
>         hstore
> --------------------
>   "a"=>"1", "b"=>"2"
> (1 row)
>
> db=> SELECT to_json('"a"=>1, "b"=>2'::hstore);
>             to_json
> ------------------------------
>   "\"a\"=>\"1\", \"b\"=>\"2\""

Works here:

business_app=> select version();
                                                            version


-----------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 9.3.5 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit
(1 row)


business_app=> SELECT to_json('"a"=>1, "b"=>2'::hstore);
        to_json
----------------------
  {"a": "1", "b": "2"}
(1 row)


Did you upgrade from a previous version of Postgres?

> (1 row)
> db=> SELECT cast(('"a"=>1, "b"=>2'::hstore) as json);
> ERROR:  cannot cast type hstore to json
> LINE 1: SELECT cast(('"a"=>1, "b"=>2'::hstore) as json);
>
> Thanks!
>    Pawel.
>


--
Adrian Klaver
adrian.klaver@aklaver.com