数据库

管理JSON和非结构化数据

在Postgres中使用JSON数据类型。

Postgres 支持存储和查询非结构化数据。

JSON 与 JSONB 对比

Postgres 支持两种 JSON 列类型：json（以字符串形式存储）和 jsonb（以二进制形式存储）。在绝大多数情况下，推荐使用 jsonb 类型。

  • json 存储输入文本的精确副本。数据库函数每次执行时都必须重新解析内容。
  • jsonb 以分解后的二进制格式存储数据。虽然由于额外的转换开销导致输入速度稍慢，但处理速度显著更快，因为不需要重新解析。

何时使用 JSON/JSONB

通常，当您需要存储非结构化数据或具有可变模式的数据时，应该使用 jsonb 列。例如，如果您想存储各种 webhook 的响应，在创建表时可能不知道响应的格式。这时可以将 payload 作为 jsonb 对象存储在单个列中。

但不要过度使用 json/jsonb 列。它们是有用的工具，但关系数据库的主要优势在于能够查询和连接结构化数据，以及由此带来的引用完整性。

创建 JSONB 列

json/jsonb 只是 Postgres 列的另一种"数据类型"。您可以像创建 text 或 int 列一样创建 jsonb 列：

1
2
3
4
5
6
create table books (  id serial primary key,  title text,  author text,  metadata jsonb);

插入 JSON 数据

您可以像插入其他数据一样插入 JSON 数据。数据必须是有效的 JSON 格式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
insert into books  (title, author, metadata)values  (    'The Poky Little Puppy',    'Janette Sebring Lowrey',    '{"description":"Puppy is slower than other, bigger animals.","price":5.95,"ages":[3,6]}'  ),  (    'The Tale of Peter Rabbit',    'Beatrix Potter',    '{"description":"Rabbit eats some vegetables.","price":4.49,"ages":[2,5]}'  ),  (    'Tootle',    'Gertrude Crampton',    '{"description":"Little toy train has big dreams.","price":3.99,"ages":[2,5]}'  ),  (    'Green Eggs and Ham',    'Dr. Seuss',    '{"description":"Sam has changing food preferences and eats unusually colored food.","price":7.49,"ages":[4,8]}'  ),  (    'Harry Potter and the Goblet of Fire',    'J.K. Rowling',    '{"description":"Fourth year of school starts, big drama ensues.","price":24.95,"ages":[10,99]}'  );

查询JSON数据

查询JSON数据与其他数据查询类似，但有一些特殊功能可以访问嵌套值。

Postgres支持一系列JSON函数和操作符。例如，->操作符返回jsonb类型的数据。如果想返回text类型的数据，可以使用->>操作符。

1
2
3
4
5
6
7
select  title,  metadata ->> 'description' as description, -- 返回text类型  metadata -> 'price' as price,  metadata -> 'ages' -> 0 as low_age,  metadata -> 'ages' -> 1 as high_agefrom books;

JSON 数据验证

Supabase 提供了 pg_jsonschema 扩展，该扩展支持根据 JSON Schema 文档验证 json 和 jsonb 数据类型。

启用扩展后，您可以向表中添加"检查约束"来验证 JSON 数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create table customers (  id serial primary key,  metadata json);alter table customersadd constraint check_metadata check (  json_matches_schema(    '{        "type": "object",        "properties": {            "tags": {                "type": "array",                "items": {                    "type": "string",                    "maxLength": 16                }            }        }    }',    metadata  ));

相关资源