管理 JSON 和非结构化数据

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

🌐 Postgres supports storing and querying unstructured data.

JSON 与 JSONB #

🌐 JSON vs JSONB

Postgres 支持两种类型的 JSON 列：json（存储为字符串）和 jsonb（存储为二进制）。几乎在所有情况下，推荐使用 jsonb 类型。

🌐 Postgres supports two types of JSON columns: json (stored as a string) and jsonb (stored as a binary). The recommended type is jsonb for almost all cases.

• json 存储输入文本的精确副本。数据库函数每次执行时都必须重新解析内容。
• jsonb 以分解的二进制格式存储数据库。虽然由于增加的转换开销，输入速度会稍微慢一点，但处理速度快得多，因为不需要重新解析。

什么时候使用 JSON/JSONB #

🌐 When to use JSON/JSONB

通常，当你的数据是非结构化的或具有可变模式时，你应该使用 jsonb 列。例如，如果你想存储各种 webhook 的响应，你在创建表格时可能不知道响应的格式。相反，你可以把 payload 作为一个 jsonb 对象存储在单独的一列里。

🌐 Generally you should use a jsonb column when you have data that is unstructured or has a variable schema. For example, if you wanted to store responses for various webhooks, you might not know the format of the response when creating the table. Instead, you could store the payload as a jsonb object in a single column.

不要在 json/jsonb 列上过度使用。它们是一个有用的工具，但关系型数据库的大部分好处来自于能够查询和连接结构化数据，以及由此带来的参照完整性。

🌐 Don't go overboard with json/jsonb columns. They are a useful tool, but most of the benefits of a relational database come from the ability to query and join structured data, and the referential integrity that brings.

创建 JSONB 列 #

🌐 Create JSONB columns

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

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

插入 JSON 数据 #

🌐 Inserting JSON data

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

🌐 You can insert JSON data in the same way that you insert any other data. The data must be valid JSON.

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

查询 JSON 数据 #

🌐 Query JSON data

查询 JSON 数据和查询其他数据类似，只是多了几个访问嵌套值的功能。

🌐 Querying JSON data is similar to querying other data, with a few other features to access nested values.

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

🌐 Postgres support a range of JSON functions and operators. For example, the -> operator returns values as jsonb data. If you want the data returned as text, use the ->> operator.

1
select
2
  title,
3
  metadata ->> 'description' as description, -- returned as text
4
  metadata -> 'price' as price,
5
  metadata -> 'ages' -> 0 as low_age,
6
  metadata -> 'ages' -> 1 as high_age
7
from books;

验证 JSON 数据 #

🌐 Validating JSON data

Supabase 提供了 pg_jsonschema 扩展，它增加了根据 JSON Schema 文档验证 json 和 jsonb 数据类型的能力。

🌐 Supabase provides the pg_jsonschema extension that adds the ability to validate json and jsonb data types against JSON Schema documents.

一旦你启用了扩展，你就可以在表中添加“检查约束”来验证 JSON 数据：

🌐 Once you have enabled the extension, you can add a "check constraint" to your table to validate the JSON data:

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

资源 #

🌐 Resources

• Postgres：JSON 函数和操作符
• Postgres JSON 类型