Logo
Section 1 / 8

处理复杂数据类型

学习如何处理各种复杂的数据类型，例如数组和 JSON 对象。

导入

处理复杂数据类型

学习如何处理各种复杂的数据类型，例如数组和 JSON 对象。

1CREATE TABLE Person (
2   id SERIAL PRIMARY KEY,
3   hobbies TEXT[]
4);
1INSERT INTO
2  Person (hobbies)
3VALUES
4  (ARRAY ['reading', 'hiking', 'cooking']);
5
6SELECT hobbies[1] FROM Person;
1SELECT hobbies FROM Person WHERE id=1;

数组操作符与函数

操作符/函数说明
ARRAY[]使用指定元素创建数组
||连接两个数组
&&判断两个数组是否有重叠元素（有共同元素时返回真）
=判断两个数组是否相等
array_append(anyarray, anyelement)将新元素添加到数组末尾
array_prepend(anyarray, anyelement)将新元素添加到数组开头
array_remove(anyarray, anyelement)从数组中移除所有指定元素的出现
array_length(anyarray, int)返回数组中指定维度的元素数量
1CREATE TABLE Person (
2    id SERIAL PRIMARY KEY,
3    address JSON
4);
5
6INSERT INTO 
7  Person (address) 
8VALUES 
9  (
10    '{"street": "123 Main St", "city": "New York", "state": "NY", "zipcode": "A2N9ZJ"}'
11  ); 
12
13SELECT address -> 'city' AS "City" FROM Person;
14
15SELECT '\n' AS " "; -- 添加换行
16
17SELECT JSON(address) AS "Address" FROM Person;
18
19SELECT '\n' AS " "; -- 添加换行
20
21SELECT address ->> 'city' AS "City" FROM Person;
22
23SELECT '\n' AS " "; -- 添加换行

JSON 操作符

操作符说明
->访问 JSON 对象中的键，返回 JSON 值
->>以文本格式获取指定键对应的值
-从 JSON 对象中删除某个键及其值
#-删除指定路径上的元素
@> 和 <@判断一个 JSON 数据是否包含在另一个中（@> 表示左包含右，<@ 表示右包含左）
||合并两个 JSON 对象
?检查 JSONB 对象中是否存在指定键
?&检查是否所有给定的键都存在
?|检查任意一个给定的键是否存在
1SELECT 
2  '{"street": "123 Main St", "city": "New York", "state": "NY"}'::jsonb 
3  AS "Address";
4
5SELECT '\n' AS " "; -- 添加换行
6
7SELECT 
8  '{"street": "123 Main St", "city": "New York", "state": "NY"}'::jsonb 
9  @> '{"state": "NY"}'::jsonb AS "Is NY state?";
10
11SELECT '\n' AS " "; -- 添加换行
12
13SELECT 
14  '{"state": "WA"}'::jsonb <@
15  '{"street": "123 Main St", "city": "New York", "state": "NY"}'::jsonb 
16AS "Is WA state?";
17
18SELECT '\n' AS " "; -- 添加换行
19
20SELECT 
21  '{"street": "123 Main St", "city": "New York", "state": "NY"}'::jsonb 
22  ? 'city' 
23AS "contains key city?";
24
25SELECT '\n' AS " "; -- 添加换行
26
27SELECT 
28  '{"street": "123 Main St", "city": "New York", "state": "NY"}'::jsonb 
29  ?& array['street', 'city', 'state'] 
30AS "contains ALL address fields?";
31
32SELECT '\n' AS " "; -- 添加换行
33
34SELECT 
35  '{"street": "123 Main St", "city": "New York", "state": "NY"}'::jsonb 
36  ?| array['street', 'city', 'state', 'zipcode'] 
37AS "contains ANY address fields";
38
39SELECT '\n' AS " "; -- 添加换行
40
41SELECT 
42  '{"street": "123 Main St", "city": "New York", "state": "NY"}'::jsonb
43  || '{"zipcode": "A2N9ZJ"}'::jsonb AS "address with zipcode";
44
45SELECT '\n' AS " "; -- 添加换行
46
47SELECT 
48  '{"street": "123 Main St", "city": "New York", "state": "NY"}'::jsonb
49  - 'state' AS  "address without state";
50
51SELECT '\n' AS " "; -- 添加换行
52
53SELECT 
54  '["Jane", "John"]'::jsonb - 1 
55  AS "removing item at index 1 from the list";
56
57SELECT 
58  '["Jane", {"Mary":1, "Steve":2}]'::jsonb #- '{1, "Steve"}' 
59  AS "removing item at specified path";

JSON 函数

函数说明
json_array_length()返回 JSON 数据中数组的长度
json_each()遍历 JSON 对象的键值对，每项作为记录返回（值为 JSON 格式）
json_each_text()遍历 JSON 对象的键值对，每项作为记录返回（值为文本格式）
json_object()根据输入值构造一个 JSON 对象
jsonb_object()根据输入值构造一个 JSONB 对象
1SELECT
2  json_array_length('["Jane", "John"]') AS "Number of fields";
3
4SELECT '\n' AS " "; -- 添加换行
5
6SELECT * FROM
7  json_each(
8    '{"street": "123 Main St", "city": "New York", "state": "NY"}'
9  );
10
11SELECT '\n' AS " "; -- 添加换行
12
13SELECT * FROM
14  json_each_text(
15    '{"street": "123 Main St", "city": "New York", "state": "NY"}'
16  );
17
18SELECT '\n' AS " "; -- 添加换行
19
20SELECT 
21  json_object(
22    '{"street", "city", "state"}', '{"123 Main St", "New York", "NY"}'
23  ) AS "JSON object";
24
25SELECT '\n' AS " "; -- 添加换行
26
27SELECT 
28  jsonb_object(
29    '{"street", "city", "state"}', '{"123 Main St", "New York", "NY"}'
30  ) AS "JSONB object";
1CREATE TABLE parks (
2   id SERIAL PRIMARY KEY, 
3   name TEXT, 
4   boundary POLYGON
5); 
6
7INSERT INTO
8  parks (name, boundary)
9VALUES
10  (
11    'Central Park',
12    '((-73.9677, 40.7936),  (-73.9668, 40.7911), (-73.9612, 40.7923), 
13     (-73.9606, 40.7941), (-73.9645, 40.7954), (-73.9677, 40.7936))'
14  );
15
16SELECT * FROM parks;
1CREATE TABLE Users (
2  user_id SERIAL PRIMARY KEY,
3  user_name VARCHAR(255),
4  user_ip_address INET,
5  user_mac_address MACADDR,
6  user_location VARCHAR(255),
7  user_status BIT
8);
9
10INSERT INTO
11  Users (
12    user_name,
13    user_ip_address,
14    user_mac_address,
15    user_location, 
16    user_status
17  )
18VALUES
19  (
20    'John Doe',
21    '192.168.0.1',
22    '00:11:22:33:44:55',
23    'California', 
24    B'1'
25  ),
26  (
27    'Jane Smith',
28    '10.0.0.1',
29    '01:02:03:04:05:06',
30    'New York',
31    B'0'
32  );
33
34SELECT * FROM users;

SQL 基础知识

SQL中的正则表达式