当前位置：首页 > news >正文

PostgreSQL提取JSON格式的数据（包含提取list指定索引数据）

news 文章来源：https://blog.csdn.net/weixin_42949219/article/details/139984057 2024/11/25 19:24:18

PostgreSQL提取JSON格式的数据（包含提取list指定索引数据）

->>, ->, #>, #>>

在PostgreSQL中，处理json或jsonb类型数据时，->>, ->, #> 和 #>> 是非常有用的操作符，它们允许你以不同的方式访问JSON对象或数组中的元素。以下是这些操作符的详细解释：

‌->> 操作符‌：
从JSON对象中提取文本（字符串）值。
如果指定的键不存在，返回NULL。
示例：json_col->>‘name’ 从json_col列中提取名为name的键对应的文本值。

‌-> 操作符‌：
从JSON对象中提取json或jsonb类型的值（即另一个JSON对象或数组）。
如果指定的键不存在，返回NULL，但返回值的类型仍然是json或jsonb。
示例：json_col->‘address’ 从json_col列中提取名为address的键对应的jsonb对象。

‌#> 操作符‌：
用于访问JSON数组中的元素，其中跟随的是数组的索引（从0开始，遵循JSON的索引习惯）。
如果索引超出数组的范围，返回NULL。
返回的是json或jsonb类型的值，具体取决于你的列类型。
示例：json_col#>‘{phones,1}’ 从json_col列中名为phones的数组中提取索引为1的元素（注意这是第二个元素，因为索引从0开始）。

‌#>> 操作符‌：
类似于#>，但返回的是文本（字符串）值，而不是json或jsonb类型的值。
如果索引超出数组的范围，返回NULL。
示例：json_col#>>‘{phones,0}’ 从json_col列中名为phones的数组中提取索引为0的元素，并返回其文本表示。

重要提示‌：
->> 和 -> 主要用于访问JSON对象的顶层或嵌套元素。要访问更深层次的嵌套元素，你可能需要组合使用这些操作符。
#> 和 #>> 主要用于访问JSON数组中的元素。要访问整个数组或进行更复杂的查询，你可能需要结合其他函数或操作符。
在使用这些操作符时，请确保你的JSON数据是有效的，并且你正在访问的键或索引确实存在于JSON数据中。

如果你的字段是文本类型，需要转换为jsonb或者json类型，并且确保你的字段值都是有效的json字符串，否则会报错。
判断是否是有效的json格式的字符串：
https://blog.csdn.net/weixin_42949219/article/details/139988773

综合示例‌：

假设我们有一个名为users的表，它有一个jsonb类型的列info，其中包含以下JSON数据：

Copy Code
{"id": 1,"name": "John Doe","email": "john.doe@example.com","preferences": {"notifications": true,"language": "en"},"phone_numbers": ["123-456-7890", "098-765-4321"]
}

我们可以使用上述操作符来访问这个JSON数据中的不同元素：
获取用户的姓名（文本）：SELECT info->>‘name’ FROM users;
获取用户的偏好设置（jsonb对象）：SELECT info->‘preferences’ FROM users;
获取用户是否启用通知（布尔值，作为文本）：SELECT info->‘preferences’->>‘notifications’ FROM users;
获取用户的第一个电话号码（文本）：SELECT info#>>‘{phone_numbers,0}’ FROM users;
通过组合使用这些操作符，你可以轻松地访问和操作存储在json或jsonb列中的复杂JSON数据。

拓展函数：
jsonb对象

select jsonb_array_elements(info->'phone_numbers');
输出：
"123-456-7890"
"098-765-4321"

select jsonb_array_element(info->'phone_numbers', 0);
输出：
"123-456-7890"

文本对象

select jsonb_array_elements_text(info->'phone_numbers');
输出：
123-456-7890
098-765-4321

select jsonb_array_element_text(info->'phone_numbers', 0);
输出：
123-456-7890