postgresql-9.3 - 如何在 postgresql 9.3 中的 JSON 类型列上使用 JSON 运算符

Question

谁能明白为什么这不起作用？根据section 9.15手册中的 ->运算符(operator)应该访问 JSON 数据类型的元素。在我看来，虽然信息架构说该列是“json”类型，但它仍然是一个标量字符串(请注意显示时的引号。)

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

更新:

我找到了这两个帖子 here和 here这表明它应该完全像我一样工作。只是为了确定我试过这个:

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

是否有我需要获取 JSON 功能的构建选项或贡献模块？

Answer 1

看来我的错误是在插入数据时使用了 to_json() 函数。这导致了一个包含我的数据的 JSON 编码字符串。我在 postgresql 文档中没有找到任何显示如何插入 JSON 数据的内容，但我最终找到了这篇文章 here这显示了一个例子。我应该这样做:

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)

(注意数据列中的值没有引号。)

现在它起作用了:

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