How to use JSON operators on JSON typed column in postgresql 9.3

Question

Can anyone see why this does not work? According to section 9.15 of the manual, the -> operator should access elements of a JSON data type. It looks to me like although the info schema says the column is type 'json' it is still really a scalar string (note the quotes when it is displayed.)

postgres=# create table jtest (id serial, data json);
CREATE TABLE
postgres=# select column_name, data_type from information_schema.columns where table_name = 'jtest';
 column_name | data_type
-------------+-----------
 id          | integer
 data        | json
(2 rows)

postgres=# insert into jtest (data) values (to_json('{"k1": 1, "k2": "two"}'::text));
INSERT 0 1
postgres=# select * from jtest;
 id |              data
----+--------------------------------
  1 | "{\"k1\": 1, \"k2\": \"two\"}"
(1 row)

postgres=# select data->'k1' from jtest;
ERROR:  cannot extract element from a scalar
postgres=# select data::json->'k1' from jtest;
ERROR:  cannot extract element from a scalar
postgres=# \q
$ pg_ctl --version
pg_ctl (PostgreSQL) 9.3beta2

Update:

I found these two posts here and here that indicate it should work exactly as I am doing. Just to be sure I tried this:

postgres=# select * from jtest where data ->> 'k2' = 'two';
ERROR:  cannot extract element from a scalar

Is there a build option or contrib module I need to get JSON functionality?

Answer 1

It appears that my mistake was using the to_json() function when inserting the data. That resulted in a JSON-encoded string containing my data. I did not find anything in the postgresql docs showing how to insert JSON data, but I did eventually find this post here that showed an example. I should have done:

postgres=# insert into jtest (data) values ('{"k1": 1, "k2": "two"}');
INSERT 0 1
postgres=# select * from jtest;
 id |          data
----+------------------------
  1 | {"k1": 1, "k2": "two"}
(1 row)

(note the lack of quotes on the value in the data column.)

Now it works:

postgres=# select * from jtest where data ->> 'k2' = 'two';
 id |          data
----+------------------------
  1 | {"k1": 1, "k2": "two"}
(1 row)