一、定义
JSON数据类型用于存储JSON(JavaScript Object Notation,JavaScript对象表示法)数据,如RFC 71594所规定。这些数据也可以存储为“text”,但是JSON数据类型的优点是,根据JSON规则,存储的每个值都认为是有效的。对于按这些数据类型存储的数据,还提供了各种特定于JSON的函数和操作符;
PostgreSQL提供了两种类型来存储JSON数据:json和jsonb。为了对这些数据类型实现高效的查询机制,PostgreSQL还提供了jsonpath数据类型。
二、可用于JSON数据类型的操作符
操作符 | 返回类型 | 描述 | 示例 | 结果 |
---|---|---|---|---|
json -> integer jsonb -> integer | json jsonb | 提取JSON数组的第n个元素(数组元素从0开始索引,但负整数从末尾开始计数)。 | select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2; select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> -3; | {"c":"baz"} {"a":"foo"} |
json -> text jsonb -> text | json jsonb | 使用给定的键提取JSON对象字段。 | select '{"a": {"b":"foo"}}'::json -> 'a'; | {"b":"foo"} |
json ->> integer jsonb ->> integer | text text | 提取JSON数组的第n个元素作为文本。 | select '[1,2,3]'::json ->> 2; | 3 |
json ->> text jsonb ->> text | text text | 提取具有给定键的JSON对象字段,作为文本。 | select '{"a":1,"b":2}'::json ->> 'b'; | 2 |
json #> text[] jsonb #> text[] | json jsonb | 提取指定路径的JSON子对象,其中路径元素可以是字段键或数组索引。 | select '{"a": {"b": ["foo","bar"]}}'::json #> '{a,b,1}'; | "bar" |
json #>> text[] jsonb #>> text[] | text text | 将指定路径下的JSON子对象提取为文本。 | select '{"a": {"b": ["foo","bar"]}}'::json #>> '{a,b,1}'; | bar |
注意:
如果JSON输入没有匹配请求的正确结构,字段/元素/路径提取操作符将返回NULL,而不是失败;例如,如果不存在这样的键或数组元素。
三、示例
select '{"age":[39,42]}'::json #> '{"age",0}'; --结果: 39
select '{"age":[39,42]}'::json #> '{"age",1}'; --结果: 42
select '["a","b"]'::json -> 0; --结果: "a"
select '["a","b"]'::json -> 1; --结果: "b"
select '[ "a", "b", {"c1": true, "c2": false} ]'::jsonb #> '{-1,"c2"}'; --结果: false