Postgres支持"JSON"已经有近10年了。10年前,宣布支持JSON时,官方只是验证了JSON是有效的,然后把它放入一个标准的文本字段。两年后的2014年,在Postgres 9.4中,官方通过JSONB
数据类型获得了更合适的JSON支持。在Postgres 14中,JSONB的支持变得更好了。
这是Postgres 14中的一个微小但令人难以置信的变化。首先需要对JSON和JSONB之间的区别做一些总结性的声明。JSON仍然存在于Postgres中,如果这样做:CREATE TABLE foo (id serial, mycolumn JSON);
将得到一个JSON数据类型。这种数据类型将确保在其中插入有效的JSON,但会将其存储为文本。如果不想对大部分JSON进行索引,只想快速插入大量的JSON,这是很有用的(一个很好的例子是记录API/日志输入)。
与JSON不同,JSONB压缩了数据,并且不保留空白。JSONB在GIN索引中也有一些更好的索引能力。 虽然可以索引JSON,但你必须对每个路径进行索引。
14版的JSON有什么新特点
如果是一个电子商务网站,想把产品目录放在Postgres中。在Crunchy的情况下,你的模式可能看起来是这样的。
CREATE TABLE products (
id serial,
name text,
price numeric(10, 2),
created_at timestamptz,
updated_at timestamptz,
deleted_at timestamptz,
details jsonb
);
经常可以看到JSON数据类型与其他标准数据类型混合在一起。在产品目录中就是一个很好的例子。你可能有两种非常不同的产品,而不是为shirts
和couches
建立一个表,你可以为每个产品提供不同的细节。对于一件衬衫,你有一个尺寸、颜色、类型(指短袖/长袖)。对于一个沙发,你可能有一个宽度,一个高度,一个类型(爱情座椅和沙发)。
在Postgres 13和早期的版本中,如果你想找到所有霓虹黄色的中号衬衫,你可以写一些查询。
SELECT *
FROM shirts
WHERE details->'attributes'->>'color' = 'neon yellow'
AND details->'attributes'->>'size' = 'medium';
那种使用->
和->>
,虽然很熟悉这种操作,但依旧感觉很痛苦。基本上有两个不同的操作符,一个用来遍历JSON文档->
,然后你会添加一个额外的>
,以提取文本的值。
不过在Postgres 14中:
SELECT *
FROM shirts
WHERE details['attributes']['color'] = '"neon yellow"'
AND details['attributes']['size'] = '"medium"'
想更新一个特定的记录一样简单:
UPDATE shirts
SET details['attributes']['color'] = '"neon blue"'
WHERE id = 123;
当然,你仍然可以使用->
和->>
,但14中支持的新下标语法是你的新选择。
为你的JSON建立索引
在JSON和JSONB中,你可以为你的JSON建立索引,以加快读取时间。在非JSONB格式中,你需要对特定的键进行索引,以便能够对它们进行查询。如果你想对颜色进行索引:
CREATE INDEX idx_products_details ON products ((details->'attributes'->'color'));
如果你想对大小进行索引:
CREATE INDEX idx_products_details ON products ((details->'attributes'->'size'));
如果你有一个完整的带有不同属性的产品目录。在JSONB
,你可以:
CREATE index idx_json_details ON json_test using gin (details);
现在GIN
索引有了很大的帮助,不必对JSON文档中的每一个不同的键进行索引,但是我们仍然需要了解,如何在目前的情况下进行最佳搜索。目前,GIN索引要求你在查询时仍然使用不同的操作符。因此,为了找到所有的黄色霓虹灯衬衫,你必须用@>
操作符来构造你的查询,以获得一个包含。
SELECT *
FROM products
WHERE details @> '{"color": "neon yellow"}';
而通过EXPLAIN ANALYZE
,我们可以看到它使用了一个索引。
EXPLAIN ANALYZE SELECT * FROM products WHERE details @> '{"color": "neon yellow"}';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on products (cost=36.25..159.79 rows=32 width=279) (actual time=0.090..0.090 rows=1 loops=1)
Recheck Cond: (details @> '{"color": "neon yellow"}'::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_json_details (cost=0.00..36.24 rows=32 width=0) (actual time=0.085..0.085 rows=1 loops=1)
Index Cond: (details @> '{"color": "neon yellow"}'::jsonb)
Planning Time: 0.140 ms
Execution Time: 0.112 ms
(7 rows)
在JSON中的Postgres,确实越来越好
Postgres 14使JSON变得比以前更容易使用。虽然不建议在应用程序中简单地使用下标格式,但对于简单的查询来说确实很有用。随着时间的推移,相信会进一步改进。