PostgreSQL 的 JSON 格式支持

PostgreSQL 9.3 以后支持 json/jsonb 格式。看来 json 是稳坐交换格式第一把座椅了。 json 和 jsonb 的主要区别是 json 按文本格式存储，本质上是 text 格式加上语法检查而已。jsonb 是按二进制存储，速度更快（省去转换过程），功能更多（支持索引，查找等功能）。所以基本上 json 格式没什么特别的价值，这里我们主要研究 jsonb 格式。

读取操作

首先是读取子项（支持 Object 和 Array 的读取）：

1
2
3
4
5
6
7
8


postgre=> select '{"foo":1, "bar":2}'::jsonb->'bar';
 ?column?
----------
 2
postgre=> select '["foo", "bar"]'::jsonb->1;
 ?column?
----------
 "bar"

然后是按 path 方式读取：

1
2
3
4


postgre=> select '{"foo":[1,2], "bar":2}'::jsonb#>'{foo,1}';
 ?column?
----------
 2

比较操作

@> 比较操作符，用以比较左边的 object/array 是不是包含了右边所有的键和值（右边的::jsonb可以省略，系统会自动转换）：

1
2
3
4


select '[1, 2, 3]'::jsonb @> '[1, 2]'::jsonb;  # true
select '[1, 2]'::jsonb @> '[1, 2, 3]'::jsonb;  # false
select '{"foo": 1, "bar": 2}'::jsonb @> '{"foo": 3}'::jsonb;   # false
select '{"foo": 1, "bar": 2}'::jsonb @> '{"foo": 1}'::jsonb;   # true

** ? 检查存在操作符，对 object 检查键是否存在，对 array 检查值是否存在，对原生类型检查是否匹配：**

1
2
3


select '{"foo": 1, "bar": 2}'::jsonb ? 'foo';   # true
select '["foo", "bar"]'::jsonb ? 'foo';         # true
select '"foo"'::jsonb ? 'foo';                  # true

关于索引

索引是查找速度的保证，由于 jsonb 是一种复合型结构，对它进行索引跟对原生类型有些区别。需要根据我们的业务逻辑中对查找方式去进行设计。

针对 @> 操作符，可以直接建立对列进行 gin 索引：

1

CREATE INDEX index_name ON table_name USING gin ( json_column_name );

这个索引会在我们使用 @> 操作时被使用：

1

SELECT * FROM table_name WHERE json_column_name @> 'josn_string';

针对需要对 json_column_name 下面某个子元素进行比较的情况：

1

CREATE INDEX index_name ON table_name USING gin ( json_column_name_and_path );

相应的，当执行以下查询的时候这个索引会被使用：

1

SELECT * FROM table_name WHERE json_column_name->sub_key ? 'key';

假如我们有个 table 叫 api，里面有个列叫 jdoc，jdoc 的内容如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

然后建立索引：

1
2


CREATE INDEX idx_jdoc ON api USING gin (jdoc);
CREATE INDEX idx_jdoc_tags ON api USING gin ((jdoc->'tags'));

执行查询：

1
2
3


SELECT * FROM api WHERE jdoc @> '{"company": "Magnafone"}';   # 这里会使用到 idx_jdoc 索引；
SELECT * FROM api WHERE jdoc->'tags' ? 'qui';                 # 这里会使用到 idx_jdoc_tags 索引；
SELECT * FROM api WHERE jdoc @> '{"tags": ["qui"]}';          # 这里会使用到 idx_jdoc 索引；

我们注意到第三条SQL可以覆盖到 idx_jdoc_tags 索引的应用场景，但像这种简单的包含判断可能很多时候不够用， idx_jdoc_tags 还是可以有很多应用场景的。另外由于 idx_jdoc 会索引 json 对象所有的键值，生成的数据会比较多，速度相对也没有 idx_jodc_tags 快。idx_jdoc_tags 只索引 tags 部分的值。

Contents

读取操作

比较操作

关于索引