PostgreSQL jsonb

这篇具有很好参考价值的文章主要介绍了PostgreSQL jsonb。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

PostgreSQL jsonb

jsonb 函数以及操作符

在PostgreSQL中,有许多用于处理JSONB数据类型的内置函数和操作符。下面列出了一些常用的JSONB函数和操作符:

  1. jsonb_pretty(jsonb) 该函数将JSONB数据格式化为易读的多行字符串。
  2. jsonb_typeof(jsonb) 该函数返回给定JSONB值的类型(例如,字符串、数值、布尔值、数组、对象等)。
  3. jsonb_array_length(jsonb) 该函数返回JSONB数组的长度。
  4. jsonb_extract_path(jsonb, VARIADIC text[]) 该函数按路径提取JSONB对象中的值。路径可以是一个或多个键名。使用VARIADIC关键字可以根据需要传递任意数量的路径参数。
  5. jsonb_extract_path_text(jsonb, VARIADIC text[]) 该函数与jsonb_extract_path相似,但它返回提取的值作为文本。
  6. jsonb_insert(jsonb, text[], jsonb[, boolean]) 该函数在给定的路径位置插入一个新的JSONB值。
  7. jsonb_set(jsonb, text[], jsonb[, boolean]) 该函数在给定的路径位置更新JSONB值。
  8. jsonb_delete(jsonb, VARIADIC text[]) 该函数删除JSONB对象中指定路径的键和对应的值。
  9. jsonb_strip_nulls(jsonb) 该函数从JSONB对象中删除所有值为NULL的键值对。
  10. jsonb_agg(jsonb) 该函数将多个JSONB值聚合为一个JSONB数组。
  11. jsonb_array_elements(jsonb) 该函数用于将JSON数组转换为行集,以便对数组中的每个元素进行操作
  12. with ordinality 用于在查询中返回一个带有索引的结果集
  13. row_number 是 PostgreSQL 中的一个窗口函数,用于为查询结果集中的每一行分配一个唯一的序号

除了上述函数之外,还有一些操作符可用于JSONB类型的操作:

  1. -> 该操作符按键提取JSONB对象中的值。
  2. ->> 该操作符返回提取的值作为文本。
  3. #> 该操作符按路径提取JSONB对象中的值。
  4. <@ 该操作符检查JSONB对象是否包含指定的键路径。
  5. @> 该操作符检查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 数组的列名或变量。

这个查询的步骤解释如下:

  1. jsonb_array_elements(your_json_array) 将 JSON 数组拆分为单独的 JSON 对象元素。
  2. (value->>'id')::int 提取每个对象的 id 属性,并将其转换为整数。
  3. WHERE (value->>'id')::int != 2 过滤掉 id 为 2 的对象。
  4. jsonb_agg(value) 将过滤后的 JSON 对象重新聚合成一个新的 JSON 数组。

请在实际操作中替换查询中的表名和列名,并在测试环境中验证操作结果,以确保你得到了预期的输出。同样地,在进行任何数据库操作之前,请务必备份你的数据以防止意外数据丢失。文章来源地址https://www.toymoban.com/news/detail-636012.html

到了这里,关于PostgreSQL jsonb的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • PostgreSQL Linux操作PostgreSQL数据库

    PostgreSQL教程 菜鸟教程:https://www.runoob.com/postgresql/postgresql-tutorial.html 登录PG数据库:psql -U 用户名(U需要大写) 登录PG数据库(指定主机、端口,并进入指定数据库): psql -U 用户名 -h 127.0.0.1 -p 5432 -d 数据库名 -U 登录的用户名 -h 连接的主机(默认127.0.0.1,可替换成远程主机

    2024年02月11日
    浏览(61)
  • postgresql|数据库|MySQL数据库向postgresql数据库迁移的工具pgloader的部署和初步使用

    MySQL数据库和postgresql数据库之间的差异并不多,这里的差异指的是对SQL语言的支持两者并不大,但底层的东西差异是非常多的,例如,MySQL的innodb引擎概念,数据库用户管理,这些和postgresql相比是完全不同的(MySQL用户就是用户,没有角色,postgresql有用户,有角色,但差异不

    2024年02月14日
    浏览(82)
  • PostgreSQL 创建数据库

    PostgreSQL 创建数据库可以用以下三种方式: CREATE DATABASE 命令需要在 PostgreSQL 命令窗口来执行,语法格式如下: 例如,我们创建一个 runoobdb 的数据库: createdb 命令创建数据库 createdb 是一个 SQL 命令 CREATE DATABASE 的封装。 参数说明: . dbname:要创建的数据库名。 . description:关

    2024年02月12日
    浏览(49)
  • PostgreSQL-数据库命令

    一个数据库是一个或多个模式的集合,而模式包含表、函数等。因此,完整的逻辑组织结构层次是服务器实例(PostgreSQL Server)、数据库(Database)、模式(Schema)、表(Table),以及某些其他对象(如函数)。一个PostgreSQL服务器实例可以管理多个数据库。当应用程序连接到一

    2024年02月14日
    浏览(50)
  • Postgresql数据库死锁

    ERROR: deadlock detected DETAIL: Process 95 waits for ShareLock on transaction 3553457; blocked by process 187. Process 187 waits for ShareLock on transaction 3553458; blocked by process 95. HINT: See server log for query details. CONTEXT: while updating tuple (0,6) in relation “deadlock_example” 其中 Process 95 在等待共享锁(ShareLock)的事务

    2024年01月20日
    浏览(64)
  • PostgreSQL连接指定数据库

    要连接到PostgreSQL中的指定数据库,您需要使用以下格式的连接字符串: 其中,username是连接PostgreSQL的用户名,password是该用户的密码,hostname是PostgreSQL服务器的主机名或IP地址,port是PostgreSQL服务器的端口号,database_name是要连接的数据库名称。 例如,如果您要连接到名为my

    2024年02月11日
    浏览(47)
  • postgresql-数据库与模式

    数据库管理系统( DBMS )是用于管理数据库的软件系统。常见的关系型DBMS有PostgreSQL、 MySQL、Oracle、Microsoft SQL Server、SQLite 等。常见的 NoSQL 数据库有 Redis、MongoDB、 Cassandra、Neo4j 等。PostgreSQL 荣获了数据库排名网站DB-Engines 2017、2018 以及 2020 年度数据库管理系统称号 PostgreSQL 数

    2024年02月08日
    浏览(51)
  • postgresql 数据库 索引 介绍

    大家在学习数据库的时候,是不是常常听到索引?那什么是索引呢?索引有哪些作用呢?索引有哪些种类呢?为什么要建索引呢?带着这些疑问,本文带你一起学习postgresql数据库的索引。 索引是提高数据库性能的常用途径。比起没有索引,使用索引可以让数据库服务器更快

    2024年02月13日
    浏览(57)
  • PGSQL(PostgreSQL)数据库基础篇:PostgreSQL 的 主要优点 、 劣势 、体系结构 、核心功能 、安装教程。

    1.维护者是PostgreSQL Global Development Group,首次发布于1989年6月。 2.操作系统支持WINDOWS、Linux、UNIX、MAC OS X、BSD。 3.从基本功能上来看,支持ACID、关联完整性、数据库事务、Unicode多国语言。 4.表和视图方面,PostgreSQL支持临时表,而物化视图,可以使用PL/pgSQL、PL/Perl、PL/Python或其

    2024年04月26日
    浏览(61)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包