PostgreSQL 14 可以更好地支持 JSON

738

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数据类型与其他标准数据类型混合在一起。在产品目录中就是一个很好的例子。你可能有两种非常不同的产品,而不是为shirtscouches建立一个表,你可以为每个产品提供不同的细节。对于一件衬衫,你有一个尺寸、颜色、类型(指短袖/长袖)。对于一个沙发,你可能有一个宽度,一个高度,一个类型(爱情座椅和沙发)。

在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变得比以前更容易使用。虽然不建议在应用程序中简单地使用下标格式,但对于简单的查询来说确实很有用。随着时间的推移,相信会进一步改进。

原文链接:blog.crunchydata.com/blog/better…