How to use JSON statements in a JSON-typed column in postgresql 9.3

Question

Can anyone understand why this is not working? According to section 9.15 of the manual, the -> operator must access the elements of the JSON data type. It seems to me that although the information schema indicates that the column is of type “json”, it is still a scalar string (note the quotation marks 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 that it should work exactly the same as I do. Just to make 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 a Contrib module, do I need to get JSON functions?

Answer 1

It seems like my mistake was to use the to_json () function when inserting data. This led to a JSON encoding containing my data. I did not find anything in the postgresql docs showing how to embed JSON data, but in the end I found this post here that showed an example. I had to do:

 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 absence of quotation marks in 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)