跳到主要内容

PostgreSQL JSON 函数

介绍​

在现代应用程序中，JSON（JavaScript Object Notation）已成为一种常见的数据交换格式。PostgreSQL 提供了强大的 JSON 支持，允许你存储、查询和操作 JSON 数据。本文将介绍 PostgreSQL 中的 JSON 函数，帮助你有效地处理 JSON 数据。

基本 JSON 函数​

1. json_each 和 json_each_text​

json_each 和 json_each_text 函数用于将 JSON 对象展开为键值对。json_each 返回键和值的 JSON 表示，而 json_each_text 返回键和值的文本表示。

sql
SELECT * FROM json_each('{"name": "Alice", "age": 30}');

输出：

 key  | value
------+-------
 name | "Alice"
 age  | 30

2. json_array_elements​

json_array_elements 函数用于将 JSON 数组展开为单独的元素。

sql
SELECT * FROM json_array_elements('[1, 2, 3]');

输出：

 value
-------
 1
 2
 3

3. json_extract_path​

json_extract_path 函数用于从 JSON 对象中提取指定路径的值。

sql
SELECT json_extract_path('{"name": "Alice", "age": 30}', 'name');

输出：

 json_extract_path
-------------------
 "Alice"

高级 JSON 函数​

1. jsonb_set​

jsonb_set 函数用于更新 JSONB 对象中的值。它接受四个参数：原始 JSONB 对象、路径、新值和一个布尔值，指示是否创建缺失的路径。

sql
SELECT jsonb_set('{"name": "Alice", "age": 30}'::jsonb, '{age}', '31');

输出：

 jsonb_set
-----------
 {"name": "Alice", "age": 31}

2. jsonb_insert​

jsonb_insert 函数用于在 JSONB 对象中插入新值。如果路径已经存在，则不会覆盖现有值。

sql
SELECT jsonb_insert('{"name": "Alice", "age": 30}'::jsonb, '{city}', '"New York"');

输出：

 jsonb_insert
--------------
 {"name": "Alice", "age": 30, "city": "New York"}

3. jsonb_delete​

jsonb_delete 函数用于从 JSONB 对象中删除指定的键。

sql
SELECT jsonb_delete('{"name": "Alice", "age": 30}'::jsonb, 'age');

输出：

 jsonb_delete
--------------
 {"name": "Alice"}

实际应用场景​

场景 1：存储和查询用户配置文件​

假设你有一个用户配置文件表，其中包含 JSONB 类型的 profile 列。你可以使用 JSON 函数来查询和更新用户的配置文件。

sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

INSERT INTO users (profile) VALUES ('{"name": "Alice", "age": 30, "city": "New York"}');

查询用户的年龄：

sql
SELECT profile->>'age' AS age FROM users WHERE id = 1;

输出：

 age
-----
 30

更新用户的年龄：

sql
UPDATE users SET profile = jsonb_set(profile, '{age}', '31') WHERE id = 1;

场景 2：处理嵌套 JSON 数据​

假设你有一个包含嵌套 JSON 数据的表，你可以使用 json_extract_path 函数来提取嵌套的值。

sql
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    details JSONB
);

INSERT INTO orders (details) VALUES ('{"order_id": 123, "customer": {"name": "Alice", "age": 30}}');

提取客户的姓名：

sql
SELECT details->'customer'->>'name' AS customer_name FROM orders WHERE id = 1;

输出：

 customer_name
--------------
 Alice

总结​

PostgreSQL 提供了丰富的 JSON 函数，使得处理和操作 JSON 数据变得非常方便。通过本文的学习，你应该能够使用这些函数来查询、更新和操作 JSON 数据。希望这些知识能够帮助你在实际项目中更好地利用 PostgreSQL 的 JSON 功能。

附加资源​

练习​

  1. 创建一个包含 JSONB 列的表，并插入一些数据。
  2. 使用 json_each 函数展开一个 JSON 对象。
  3. 使用 jsonb_set 函数更新 JSONB 对象中的值。
  4. 使用 json_extract_path 函数提取嵌套 JSON 数据中的值。

通过完成这些练习，你将更好地掌握 PostgreSQL 中的 JSON 函数。