PostgreSQL jsonb
jsonb 函数以及操作符
在PostgreSQL中,有许多用于处理JSONB数据类型的内置函数和操作符。下面列出了一些常用的JSONB函数和操作符:
-
jsonb_pretty(jsonb)
该函数将JSONB数据格式化为易读的多行字符串。 -
jsonb_typeof(jsonb)
该函数返回给定JSONB值的类型(例如,字符串、数值、布尔值、数组、对象等)。 -
jsonb_array_length(jsonb)
该函数返回JSONB数组的长度。 -
jsonb_extract_path(jsonb, VARIADIC text[])
该函数按路径提取JSONB对象中的值。路径可以是一个或多个键名。使用VARIADIC
关键字可以根据需要传递任意数量的路径参数。 -
jsonb_extract_path_text(jsonb, VARIADIC text[])
该函数与jsonb_extract_path
相似,但它返回提取的值作为文本。 -
jsonb_insert(jsonb, text[], jsonb[, boolean])
该函数在给定的路径位置插入一个新的JSONB值。 -
jsonb_set(jsonb, text[], jsonb[, boolean])
该函数在给定的路径位置更新JSONB值。 -
jsonb_delete(jsonb, VARIADIC text[])
该函数删除JSONB对象中指定路径的键和对应的值。 -
jsonb_strip_nulls(jsonb)
该函数从JSONB对象中删除所有值为NULL的键值对。 -
jsonb_agg(jsonb)
该函数将多个JSONB值聚合为一个JSONB数组。 -
jsonb_array_elements(jsonb)
该函数用于将JSON数组转换为行集,以便对数组中的每个元素进行操作 -
with ordinality
用于在查询中返回一个带有索引的结果集 -
row_number
是 PostgreSQL 中的一个窗口函数,用于为查询结果集中的每一行分配一个唯一的序号
除了上述函数之外,还有一些操作符可用于JSONB类型的操作:
-
->
该操作符按键提取JSONB对象中的值。 -
->>
该操作符返回提取的值作为文本。 -
#>
该操作符按路径提取JSONB对象中的值。 -
<@
该操作符检查JSONB对象是否包含指定的键路径。 -
@>
该操作符检查JSONB对象是否包含指定的键-值对。
jsonb 功能示例
@>
@>
是 PostgreSQL 中用于比较 JSONB 数据类型的操作符,它用于判断一个 JSONB 值是否包含另一个 JSONB 值。它返回一个布尔值,如果被比较的 JSONB 值包含了目标 JSONB 值中的所有键值对,则返回 true,否则返回 false。
使用示例:
假设有一个名为 data 的 JSONB 列,包含以下数据:
{
"name": "John",
"age": 30,
"address": {
"city": "New York",
"country": "USA"
}
}
我们可以使用@>
操作符来判断 data 列中是否包含某个特定的 JSONB 值。例如,我们可以判断是否存在一个包含 “name”: “John” 的 JSONB 值:
SELECT * FROM table_name WHERE data @> '{"name": "John"}';
这将返回包含 “name”: “John” 的 JSONB 值的所有行。
我们还可以判断是否存在一个包含特定键值对的 JSONB 值。例如,我们可以判断是否存在一个包含 “address”: {“city”: “New York”} 的 JSONB 值:
SELECT * FROM table_name WHERE data @> '{"address": {"city": "New York"}}';
这将返回包含 “address”: {“city”: “New York”} 的 JSONB 值的所有行。
注意:@> 操作符只能用于比较 JSONB 值,不能用于比较 JSON 值。如果要比较 JSON 值,可以使用 ::jsonb 强制将 JSON 转换为 JSONB 类型。
not @>
在 PostgreSQL 的 JSONB 中,要判断一个 JSONB 对象是否不包含另一个 JSONB 对象,可以使用 NOT 运算符和 @> 运算符的组合。下面是一个示例代码:
-- 创建一个表,包含一个名为data的JSONB列
CREATE TABLE tb_user (
id SERIAL PRIMARY KEY,
data JSONB
);
-- 插入一些示例数据
INSERT INTO tb_user (data)
VALUES
('{"name": "John", "hobbies": ["reading", "sports"]}'::jsonb),
('{"name": "Jane", "hobbies": ["music", "painting"]}'::jsonb);
-- 查询不包含指定对象的数据
SELECT *
FROM tb_user
WHERE NOT data @> '{"name": "John", "hobbies": ["reading", "sports"]}'::jsonb;
在查询中,我们使用 NOT 运算符和 @> 运算符的组合,@> 表示包含关系,NOT 表示取反。
jsonb_build_object
在 PostgreSQL 中,jsonb_build_object 函数用于构建一个 JSONB 对象。该函数接受一个或多个键值对作为参数,并返回一个 JSONB 对象。下面是 jsonb_build_object 函数的语法:
jsonb_build_object(key1, value1, key2, value2, ...)
参数说明:
- key1, key2, …:键名。
- value1, value2, …:键对应的值。
下面是一个示例:
-- 使用 jsonb_build_object 构建 JSONB 对象
SELECT jsonb_build_object('name', 'John', 'age', 30) AS user_info;
在上述示例中,我们使用 jsonb_build_object 函数构建了一个 JSONB 对象,其中包含了 name 和 age 两个键值对。键名与对应的值可以是任意合法的表达式。
执行上述代码后,我们会得到一个 JSONB 对象:
{"name": "John", "age": 30}
通过使用 jsonb_build_object 函数,我们可以方便地构建 JSONB 对象,用于存储和处理复杂的 JSON 数据。
jsonb_insert
jsonb_insert函数的语法如下:
jsonb_insert(target jsonb, path jsonpath, new_value jsonb, skip_existing boolean DEFAULT false)
参数说明:
- target:要插入对象的目标JSONB值。
- path:指定要插入对象的路径。可以是一个JSONPath表达式,也可以是一个由键组成的数组。例如,'{hobbies, -1}'表示在名为hobbies的数组中的最后一个位置插入对象。
- new_value:要插入的新对象。
- skip_existing:可选参数,表示如果插入的对象已经存在,则是否跳过插入操作。默认值为false,表示不跳过插入。
以下是一个示例,演示如何使用jsonb_insert函数向JSONB数组中插入对象:
-- 创建一个表,包含一个名为data的JSONB列
CREATE TABLE tb_user (
id SERIAL PRIMARY KEY,
data JSONB
);
-- 插入一些示例数据
INSERT INTO tb_user (data)
VALUES
('{"name": "John", "hobbies": ["reading", "sports"]}'::jsonb),
('{"name": "Jane", "hobbies": ["music", "painting"]}'::jsonb);
-- 在hobbies数组中插入一个新的对象,但如果对象已经存在,则不进行插入
UPDATE tb_user
SET data = jsonb_insert(data, '{hobbies}', '{"new_hobby": "cooking"}'::jsonb, true)
WHERE id = 1;
-- 查询更新后的数据
SELECT * FROM tb_user;
jsonb_delete
在 PostgreSQL 中,你可以使用 jsonb_delete
函数来删除 JSONB 类型的字段。
以下是 jsonb_delete
函数的语法:
jsonb_delete(jsonb, text[])
该函数接受两个参数:要删除字段的 JSONB 对象和要删除的字段路径。这个函数返回一个新的 JSONB 对象,其中指定的字段已被删除。
-
jsonb
:JSONB 对象,要从中删除字段的对象。 -
text[]
:字段路径,指定要删除的字段。
下面是一个使用 jsonb_delete
函数的示例:
-- 创建一个包含 JSONB 数据的表
CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
data JSONB
);
-- 插入一条数据
INSERT INTO my_table (data)
VALUES ('{"name": "John", "age": 30, "address": {"city": "New York", "state": "NY"}}');
-- 删除 JSONB 对象中的字段
UPDATE my_table
SET data = jsonb_delete(data, '{address, state}')
WHERE id = 1;
-- 查询结果
SELECT * FROM my_table;
最后,字段 address
中的 state
字段已被删除。
-
要简单方便的删除name属性,也可以使用-
,
UPDATE my_table
SET data = data - 'name'
WHERE id = 1;
jsonb_agg(jsonb)
在 PostgreSQL 中,jsonb_agg 函数用于将行转换为 JSONB 数组,并将其聚合成一个 JSONB 对象。下面是 jsonb_agg 函数的语法:
jsonb_agg(expression)
参数说明:
- expression:要聚合的表达式,可以是一个列名、常量或表达式。
下面是一个示例:
假设我们有一个名为 users 的表,其中包含 name 和 age 两个列。我们希望将所有用户的信息聚合成一个 JSONB 数组。
-- 创建表
CREATE TABLE users (
id serial primary key,
name text,
age integer
);
-- 插入数据
INSERT INTO users (name, age)
VALUES ('John', 30), ('Jane', 25), ('Tom', 35);
-- 使用 jsonb_agg 聚合数据
SELECT jsonb_agg(jsonb_build_object('name', name, 'age', age)) AS users_info
FROM users;
在上述示例中,我们首先创建了一个名为 users 的表,其中包含 name 和 age 两个列。然后,我们向表中插入了几条用户数据。
接下来,我们使用 jsonb_agg 函数将用户的姓名和年龄聚合成一个 JSONB 数组,并使用 jsonb_build_object 函数构建每个用户的 JSON 对象。最后,我们使用 SELECT 语句查询并返回聚合后的结果。
执行上述代码后,我们将得到一个包含所有用户信息的 JSONB 数组。
示例输出:
[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}, {"name": "Tom", "age": 35}]
通过使用 jsonb_agg 函数,我们可以将行数据聚合成一个 JSONB 数组,便于处理和存储复杂的 JSON 数据。
jsonb_set
jsonb_set 是 PostgreSQL 中的一个函数,用于修改 JSONB 类型的值。它可以用于更新 JSONB 对象中的指定路径上的值,或者在指定路径上插入新的键值对。
以下是 jsonb_set 函数的使用说明:
语法:
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
参数说明:
- target:要修改的 JSONB 值。
- path:指定要修改的路径,以数组形式表示。每个数组元素都是一个键或索引,用于定位 JSONB 值的位置。例如,[‘a’, ‘b’, ‘c’] 表示要修改的路径为 target->‘a’->‘b’->‘c’。
- new_value:要设置的新值,必须是一个合法的 JSONB 值。
- create_missing:可选参数,指定是否在路径上创建缺失的键。默认情况下,如果路径上的键不存在,则不会创建缺失的键。如果设置为 true,则会创建缺失的键。
示例:
假设有一个名为 data 的 JSONB 对象,内容如下:
{
"name": "John",
"age": 30,
"address": {
"city": "New York",
"country": "USA"
}
}
我们想要将 data 对象中的 age 字段修改为 35,可以使用以下 SQL 查询:
UPDATE table_name
SET data = jsonb_set(data, '{age}', '35'::jsonb)
WHERE id = 1;
执行以上查询后,data 对象中的 age 字段将被修改为 35。
注意事项:
- jsonb_set 函数返回一个新的 JSONB 值,并不会直接修改原始的 JSONB 值。如果需要更新原始的 JSONB 值,需要将返回的新值赋值给原始的 JSONB 列或变量。
- jsonb_set 函数只能在 UPDATE 查询中使用,并且只能用于修改 JSONB 类型的列或变量。
- jsonb_set 函数只能修改已经存在的键值对,无法用于删除键或修改键的名称。如果需要删除键或修改键的名称,可以使用 jsonb 类型的其他函数,如 jsonb_remove 或 jsonb_set 结合 jsonb_delete。
- jsonb_set 函数也可以用于插入新的键值对,如果指定的路径不存在,且 create_missing 参数设置为 true。
问题: 函数 jsonb_set(jsonb, text, unknown, boolean) 不存在
select jsonb_set('[
{"name": "Product A", "price": 100},
{"name": "Product B", "price": 200},
{"name": "Product C", "price": 300}
]'::jsonb, '{'||1||'}', '{"name": "Product B", "price": 400}', false)
报错
> 错误: 函数 jsonb_set(jsonb, text, unknown, boolean) 不存在
LINE 1: select jsonb_set('[
^
HINT: 没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.
jsonb_set 语法 jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
需要将动态拼接的path转换为etext[]类型
select jsonb_set('[
{"name": "Product A", "price": 100},
{"name": "Product B", "price": 200},
{"name": "Product C", "price": 300}
]'::jsonb, ('{'||1||'}')::text[], '{"name": "Product B", "price": 400}', false)
jsonb_array_elements
在PostgreSQL中,jsonb_array_elements函数用于将JSON数组拆分为多行。它返回一个包含数组元素的结果集,每个元素都在单独的行中。
以下是使用jsonb_array_elements的示例:
select jsonb_array_elements('[
{"name": "Product A", "price": 100},
{"name": "Product B", "price": 200},
{"name": "Product C", "price": 300}
]'::jsonb)
结果
{"name": "Product A", "price": 100}
{"name": "Product B", "price": 200}
{"name": "Product C", "price": 300}
在这个例子中,我们将一个包含三个JSON对象的数组传递给jsonb_array_elements函数。函数将返回一个结果集,其中每个对象的属性都在单独的行中。
使用jsonb_array_elements的基本语法如下:
SELECT *
FROM jsonb_array_elements(json_array)
json_array是一个包含JSON数组的表达式或列名。jsonb_array_elements将返回一个结果集,其中每个数组元素都在单独的行中表示。
可以使用jsonb_array_elements函数的结果集进行进一步的操作,例如过滤、聚合或与其他表进行连接。
请注意,jsonb_array_elements函数只适用于JSON数组。如果传递给函数的表达式不是有效的JSON数组,将会抛出错误。
使用jsonb_array_elements可以方便地处理JSON数组的元素,使其更容易进行查询和处理。
with ordinality
在PostgreSQL中,WITH ORDINALITY用于在查询中返回一个带有索引的结果集。它通常与jsonb_array_elements函数一起使用,以获取带有索引的JSON数组元素。
以下是使用WITH ORDINALITY的示例:
SELECT *
FROM jsonb_array_elements('[{"name": "Product A", "price": 100}, {"name": "Product B", "price": 200}, {"name": "Product C", "price": 300}]'::jsonb) WITH ORDINALITY AS arr(elem, idx);
结果
elem | idx
---------------------------------------------
{"name": "Product A", "price": 100} | 1
{"name": "Product B", "price": 200} | 2
{"name": "Product C", "price": 300} | 3
在这个例子中,我们使用jsonb_array_elements函数从JSON数组中提取元素,并使用WITH ORDINALITY将结果集中的每个元素的索引添加到idx列中。结果集将包含两列:elem包含提取的元素,idx包含索引值。
使用WITH ORDINALITY的语法如下:
SELECT *
FROM 表名
WITH ORDINALITY
WITH ORDINALITY关键字必须紧跟在要返回的结果集之前,并且只能在FROM子句中的函数或子查询中使用。
请注意,WITH ORDINALITY只适用于返回多个结果的函数或子查询,如jsonb_array_elements函数。对于返回单个结果的函数或子查询,WITH ORDINALITY没有任何影响。
使用WITH ORDINALITY可以方便地处理需要索引的结果集,特别是在处理数组或其他有序集合时非常有用。
row_number
ROW_NUMBER() OVER()是在PostgreSQL中用于为查询结果中的每一行分配一个唯一的行号的窗口函数。它可以与OVER子句一起使用,以定义行号的分区和排序方式。
语法:
row_number() OVER (PARTITION BY column1, column2, ... ORDER BY column1, column2, ...)
参数说明:
-
PARTITION BY:可选参数,用于指定分组的列。如果指定了分组列,则 row_number 函数将在每个分组内进行计数,每个分组都从
1
开始递增。如果不指定,则将整个结果集作为一个分组。 -
ORDER BY:可选参数,用于指定排序的列。如果指定了排序列,则 row_number 函数将根据排序规则为每一行分配序号。如果不指定,则按照查询结果集的顺序分配序号。
示例: 假设有一个名为 users 的表,包含以下列:id、name、age。我们想要为每个用户分配一个序号,按照年龄从小到大排序。
SELECT id, name, age, row_number() OVER (ORDER BY age) as row_num
FROM users;
执行以上查询后,将返回一个包含 id、name、age、row_num 列的结果集,其中 row_num 列为每个用户分配的序号。
注意事项:
- row_number 函数在查询结果集中的每一行都会计算一次,因此可能会对查询的性能产生影响。如果不需要计算序号,可以考虑使用其他窗口函数,如 rank 或 dense_rank。
- row_number 函数返回的序号是可重复的,即如果有多个行具有相同的排序值,则它们可能会被分配相同的序号。如果需要确保序号唯一,可以考虑使用 rank 或 dense_rank 函数。
- row_number 函数只能在 SELECT 查询中使用,并且只能在窗口函数中使用。
jsonb 场景示例
1. 匹配数组中对象,修改数组中未知角标对象的字段值
-- 创建示例表
CREATE TABLE products (
id serial primary key,
details jsonb
);
-- 插入示例数据
INSERT INTO products (details)
VALUES ('[
{"name": "Product A", "price": 100},
{"name": "Product B", "price": 200},
{"name": "Product C", "price": 300}
]'::jsonb);
-- 查看原始数据
SELECT * FROM products;
-- 修改数组对象中对象字段值
UPDATE products
SET details = jsonb_set(details, ('{'|| (SELECT index-1 FROM (
SELECT jsonb_array_elements(details) WITH ORDINALITY arr(elem, index)
) AS sub WHERE elem ->> 'name' = 'Product B') ||', price}')::text[], '400', false)
WHERE details @> '[{"name": "Product B"}]';
-- 查看修改后的数据
SELECT * FROM products;
2 删除json数组中json对象
方案一
要从给定的 JSON 数组中删除 id
为 2 的对象,可以按照以下步骤进行操作:
假设你有一个 JSON 数组如下:
[
{"id": 1, "name": "Alice"},
{"id": 2, "name": "Bob"},
{"id": 3, "name": "Charlie"}
]
你可以使用以下 SQL 查询来删除 id
为 2 的对象:
SELECT jsonb_agg(value) FROM (
SELECT value FROM jsonb_array_elements(your_json_array)
WHERE (value->>'id')::int != 2
) AS filtered;
请将上述查询中的 your_json_array
替换为你的实际 JSON 数组的列名或变量。
这个查询的步骤解释如下:文章来源:https://www.toymoban.com/news/detail-636012.html
-
jsonb_array_elements(your_json_array)
将 JSON 数组拆分为单独的 JSON 对象元素。 -
(value->>'id')::int
提取每个对象的id
属性,并将其转换为整数。 -
WHERE (value->>'id')::int != 2
过滤掉id
为 2 的对象。 -
jsonb_agg(value)
将过滤后的 JSON 对象重新聚合成一个新的 JSON 数组。
请在实际操作中替换查询中的表名和列名,并在测试环境中验证操作结果,以确保你得到了预期的输出。同样地,在进行任何数据库操作之前,请务必备份你的数据以防止意外数据丢失。文章来源地址https://www.toymoban.com/news/detail-636012.html
到了这里,关于PostgreSQL jsonb的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!