PostgreSQL 中如何存储和查询 JSON 数据？

推荐答案

在 PostgreSQL 中，可以使用 JSON 和 JSONB 数据类型来存储 JSON 数据。JSONB 是二进制格式的 JSON 数据，支持索引和更高效的查询操作。

存储 JSON 数据

1. 创建表并定义 JSON 列：

   CREATE TABLE example_table (
       id SERIAL PRIMARY KEY,
       data JSONB
   );

2. 插入 JSON 数据：

   INSERT INTO example_table (data) VALUES 
   ('{"name": "Alice", "age": 30, "address": {"city": "New York", "zip": "10001"}}'),
   ('{"name": "Bob", "age": 25, "address": {"city": "San Francisco", "zip": "94105"}}');

查询 JSON 数据

1. 查询 JSON 字段：

   SELECT data->>'name' AS name FROM example_table;

2. 查询嵌套 JSON 字段：

   SELECT data->'address'->>'city' AS city FROM example_table;

3. 使用条件查询：

   SELECT * FROM example_table WHERE data->>'name' = 'Alice';

4. 使用 JSONB 操作符：

   SELECT * FROM example_table WHERE data @> '{"age": 25}';

5. 创建索引以优化查询：

   CREATE INDEX idx_data_name ON example_table ((data->>'name'));

本题详细解读

JSON 和 JSONB 的区别

• JSON：存储的是文本格式的 JSON 数据，查询时需要解析文本，效率较低。
• JSONB：存储的是二进制格式的 JSON 数据，支持索引，查询效率更高，且支持更多的操作符。

常用操作符

• ->：获取 JSON 对象的字段值，返回 JSON 类型。
• ->>：获取 JSON 对象的字段值，返回文本类型。
• #>：获取嵌套 JSON 对象的字段值，返回 JSON 类型。
• #>>：获取嵌套 JSON 对象的字段值，返回文本类型。
• @>：检查 JSONB 是否包含指定的 JSON 对象。

索引优化

对于频繁查询的 JSON 字段，可以创建索引以提高查询性能。例如，可以为 data->>'name' 创建索引：

CREATE INDEX idx_data_name ON example_table ((data->>'name'));

示例查询

1. 查询所有记录：

   SELECT * FROM example_table;

2. 查询特定字段：

   SELECT data->>'name' AS name, data->'address'->>'city' AS city FROM example_table;

3. 条件查询：

   SELECT * FROM example_table WHERE data->>'age' = '30';

4. 使用 JSONB 操作符查询：

   SELECT * FROM example_table WHERE data @> '{"address": {"city": "New York"}}';

通过这些操作，可以高效地存储和查询 JSON 数据，满足各种业务需求。