PostgreSQL中JSON函数和操作符

json和jsonb 操作符

操作符	右操作数类型	描述	例子	例子结果
`->`	`int`	获得 JSON 数组元素（索引从 0 开始，负整数从末尾开始计）	`'[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2`	`{"c":"baz"}`
`->`	`text`	通过键获得 JSON 对象域	`'{"a": {"b":"foo"}}'::json->'a'`	`{"b":"foo"}`
`->>`	`int`	以`text`形式获得 JSON 数组元素	`'[1,2,3]'::json->>2`	`3`
`->>`	`text`	以`text`形式获得 JSON 对象域	`'{"a":1,"b":2}'::json->>'b'`	`2`
`#>`	`text[]`	获取在指定路径的 JSON 对象	`'{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'`	`{"c": "foo"}`
`#>>`	`text[]`	以`text`形式获取在指定路径的 JSON 对象	`'{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'`	`3`

额外的jsonb操作符

操作符	右操作数类型	描述	例子
`@>`	`jsonb`	左边的 JSON 值是否在顶层包含右边的 JSON 路径/值项？	`'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb`
`<@`	`jsonb`	左边的 JSON 路径/值项是否被包含在右边的 JSON 值的顶层？	`'{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb`
`?`	`text`	键/元素字符串是否存在于 JSON 值的顶层？	`'{"a":1, "b":2}'::jsonb ? 'b'`
`?\|`	`text[]`	这些数组字符串中的任何一个是否做为顶层键存在？	`'{"a":1, "b":2, "c":3}'::jsonb ?\| array['b', 'c']`
`?&`	`text[]`	是否所有这些数组字符串都作为顶层键存在？	`'["a", "b"]'::jsonb ?& array['a', 'b']`
`\|\|`	`jsonb`	把两个`jsonb`值串接成一个新的`jsonb`值	`'["a", "b"]'::jsonb \|\| '["c", "d"]'::jsonb`
`-`	`text`	从左操作数删除键/值对或者string 元素。键/值对基于它们的键值来匹配。	`'{"a": "b"}'::jsonb - 'a'`
`-`	`text[]`	从左操作数中删除多个键/值对或者string元素。键/值对基于它们的键值来匹配。	`'{"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]`
`-`	`integer`	删除具有指定索引（负值表示倒数）的数组元素。如果顶层容器不是数组则抛出一个错误。	`'["a", "b"]'::jsonb - 1`
`#-`	`text[]`	删除具有指定路径的域或者元素（对于 JSON 数组，负值表示倒数）	`'["a", {"b":1}]'::jsonb #- '{1,b}'`

JSON 创建函数

函数	描述	例子	例子结果
`to_json(anyelement)` `to_jsonb(anyelement)`	把该值返回为`json`或者`jsonb`。数组和组合会被（递归）转换成数组和对象；对于不是数组和组合的值，如果有从该类型到`json`的造型，造型函数将被用来执行该转换；否则将产生一个标量值。对于任何不是数字、布尔、空值的标量类型，将使用文本表达，在这种风格下它是一个合法的 `json`或者`jsonb`值。	`to_json('Fred said "Hi."'::text)`	`"Fred said \"Hi.\""`
`array_to_json(anyarray [, pretty_bool])`	把数组作为一个 JSON 数组返回。一个 PostgreSQL 多维数组会成为一个数组的 JSON 数组。如果`pretty_bool`为真，将在第 1 维度的元素之间增加换行。	`array_to_json('{{1,5},{99,100}}'::int[])`	`[[1,5],[99,100]]`
`row_to_json(record [, pretty_bool])`	把行作为一个 JSON 对象返回。如果`pretty_bool`为真，将在第1层元素之间增加换行。	`row_to_json(row(1,'foo'))`	`{"f1":1,"f2":"foo"}`
`json_build_array(VARIADIC "any")` `jsonb_build_array(VARIADIC "any")`	从一个可变参数列表构造一个可能包含异质类型的 JSON 数组。	`json_build_array(1,2,'3',4,5)`	`[1, 2, "3", 4, 5]`
`json_build_object(VARIADIC "any")` `jsonb_build_object(VARIADIC "any")`	从一个可变参数列表构造一个 JSON 对象。通过转换，该参数列表由交替出现的键和值构成。	`json_build_object('foo',1,'bar',2)`	`{"foo": 1, "bar": 2}`
`json_object(text[])` `jsonb_object(text[])`	从一个文本数组构造一个 JSON 对象。该数组必须可以是具有偶数个成员的一维数组（成员被当做交替出现的键/值对），或者是一个二维数组（每一个内部数组刚好有 2 个元素，可以被看做是键/值对）。	`json_object('{a, 1, b, "def", c, 3.5}')` `json_object('{{a, 1},{b, "def"},{c, 3.5}}')`	`{"a": "1", "b": "def", "c": "3.5"}`
`json_object(keys text[], values text[])` `jsonb_object(keys text[], values text[])`	`json_object`的这种形式从两个独立的数组得到键/值对。在其他方面和一个参数的形式相同。	`json_object('{a, b}', '{1,2}')`	`{"a": "1", "b": "2"}`

JSON 处理

函数	返回值	描述	例子	例子结果
`json_array_length(json)` `jsonb_array_length(jsonb)`	`int`	返回最外层 JSON 数组中的元素数量。	`json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')`	`5`
`json_each(json)` `jsonb_each(jsonb)`	`setof key text, value json` `setof key text, value jsonb`	扩展最外层的 JSON 对象成为一组键/值对。	`select * from json_each('{"a":"foo", "b":"bar"}')`	key \| value -----+-------a \| "foo"b \| "bar"
`json_each_text(json)` `jsonb_each_text(jsonb)`	`setof key text, value text`	扩展最外层的 JSON 对象成为一组键/值对。返回值将是`text`类型。	`select * from json_each_text('{"a":"foo", "b":"bar"}')`	key \| value -----+-------a \| foob \| bar
`json_extract_path(from_json json, VARIADIC path_elems text[])` `jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[])`	`json` `jsonb`	返回由`path_elems`指向的 JSON 值（等效于`#>`操作符）。	`json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')`	`{"f5":99,"f6":"foo"}`
`json_extract_path_text(from_json json, VARIADIC path_elems text[])` `jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])`	`text`	以`text`返回由`path_elems`指向的 JSON 值（等效于`#>>`操作符）。	`json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')`	`foo`
`json_object_keys(json)` `jsonb_object_keys(jsonb)`	`setof text`	返回最外层 JSON 对象中的键集合。	`json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')`	json_object_keys ------------------f1f2
`json_populate_record(base anyelement, from_json json)` `jsonb_populate_record(base anyelement, from_json jsonb)`	`anyelement`	扩展`from_json`中的对象成一个行，它的列匹配由`base`定义的记录类型（见下文的注释）。	`select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}')`	a \| b \| c ---+-----------+-------------1 \| {2,"a b"} \| (4,"a b c")
`json_populate_recordset(base anyelement, from_json json)` `jsonb_populate_recordset(base anyelement, from_json jsonb)`	`setof anyelement`	扩展`from_json`中最外的对象数组为一个集合，该集合的列匹配由`base`定义的记录类型。	`select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')`	a \| b ---+---1 \| 23 \| 4
`json_array_elements(json)` `jsonb_array_elements(jsonb)`	`setof json` `setof jsonb`	把一个 JSON 数组扩展成一个 JSON 值的集合。	`select * from json_array_elements('[1,true, [2,false]]')`	value -----------1true[2,false]
`json_array_elements_text(json)` `jsonb_array_elements_text(jsonb)`	`setof text`	把一个 JSON 数组扩展成一个`text`值集合。	`select * from json_array_elements_text('["foo", "bar"]')`	value -----------foobar
`json_typeof(json)` `jsonb_typeof(jsonb)`	`text`	把最外层的 JSON 值的类型作为一个文本字符串返回。可能的类型是： `object`、`array`、`string`、`number`、 `boolean`以及`null`。	`json_typeof('-123.4')`	`number`
`json_to_record(json)` `jsonb_to_record(jsonb)`	`record`	从一个 JSON 对象（见下文的注解）构建一个任意的记录。正如所有返回`record` 的函数一样，调用者必须用一个`AS`子句显式地定义记录的结构。	`select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)`	a \| b \| c \| d \| r ---+---------+---------+---+---------------1 \| [1,2,3] \| {1,2,3} \| \| (123,"a b c")
`json_to_recordset(json)` `jsonb_to_recordset(jsonb)`	`setof record`	从一个 JSON 对象数组（见下文的注解）构建一个任意的记录集合。正如所有返回`record` 的函数一样，调用者必须用一个`AS`子句显式地定义记录的结构。	`select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);`	a \| b ---+-----1 \| foo2 \|
`json_strip_nulls(from_json json)` `jsonb_strip_nulls(from_json jsonb)`	`json` `jsonb`	返回`from_json`，其中所有具有空值的对象域都被省略。其他空值不动。	`json_strip_nulls('[{"f1":1,"f2":null},2,null,3]')`	`[{"f1":1},2,null,3]`
`jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])`	`jsonb`	返回`target`，其中由 `path`指定的节用 `new_value`替换，如果 `path`指定的项不存在并且 `create_missing`为真（默认为 `true`）则加上 `new_value`。正如面向路径的操作符一样，出现在`path`中的负整数表示从 JSON 数组的末尾开始数。	`jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false)` `jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]')`	`[{"f1":[2,3,4],"f2":null},2,null,3]` `[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]`
`jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean])`	`jsonb`	返回被插入了`new_value`的`target`。如果`path`指定的`target`节在一个 JSONB 数组中，`new_value`将被插入到目标之前（`insert_after`为`false`，默认情况）或者之后（`insert_after`为真）。如果`path`指定的`target`节在一个 JSONB 对象内，则只有当`target`不存在时才插入`new_value`。对于面向路径的操作符来说，出现在`path`中的负整数表示从 JSON 数组的末尾开始计数。	`jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')` `jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)`	`{"a": [0, "new_value", 1, 2]}` `{"a": [0, 1, "new_value", 2]}`
`jsonb_pretty(from_json jsonb)`	`text`	把`from_json`返回成一段缩进后的 JSON 文本。	`jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')`	[{"f1": 1,"f2": null},2,null,3 ]

上面内容看起来比较晦涩，其实postgreSQL中json函数的操作在实际用的时候也是比较方便的。

发文笔记本没有安装数据库工具，公司不能访问外网，就不上代码了

我在csdn已经找到同僚分析的代码部分示例，相信你看过我转载的内容，就能对json有个新的认知，一通百通。理解这一个，其他的函数，自己尝试验证一下。

一、 -> 和 ->> ：

-> 表示获取一个JSON数组元素，支持下标值(下标从0开始)、Key获取。->> 表示获取一个JSON对象字符串。

代码：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1;结果：
{"b":2}

以上，::JSON 表示声明前面的字符串为一个JSON字符串对象，而且PostgreSQL中的JSON、JSONB对象 Key的声明必须是字符串 。同时，1表示获取JSON数组中下标值为1的JSON对象。

接下来，看下 ->> 的用法：

代码：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1;
结果：
{"b":2}

以上，->> 的查询结果和 -> 对比不太直观，我们可以进一步验证。

代码：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1 -> 'b';结果：
2

以上，我们可以看到首先我们使用下标的方式，获取JSON数组中下标值为1的JSON对象 {"b":2}。然后，我们通过Key的方式来获取这个JSON对象的Value值，结果是 2。

接下来，我们测试下 ->> 的方式来获取：

代码：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';报错：
[SQL]SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';[Err] 错误:  操作符不存在: text -> unknown
LINE 1: SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

以上，可以看到错误提示操作符不存在，因为第一步查询出来的是一个字符串，不是JSON对象。当然，我们可以改造下这个查询语句：

代码：
SELECT ('[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1)::JSON -> 'b';结果：
2

以上，可以看到现在可以正确查询出结果，我们将第一步查询出来的字符串转成JSON对象，然后通过 Key 的方式来获取 Value。不过，这种查询方式相对于 -> 来说还是比较繁琐的。

二、 #> 和 #>> ：

在前一步，我们在一个JSON数组中可以使用 -> 下标值的方式来获取一个JSON对象。但是，如果我们我们检索的不是JSON数组，而是一个JSON对象中的JSON对象。很显然，这种下标获取的方式不再适用。不过，我们可以使用下面的方式来获取。

#> 表示获取指定路径的一个JSON对象，#>>表示获取指定路径的一个JSON对象的字符串。

代码：
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #> '{b}'结果：
{"ba":"b1","bb":"b2"}

以上，我们使用 #> 方式来获取一个JSON对象中的JSON对象。

注意：

在获取一个JSON对象时，除非是JSON数组中的下标，必须要要用 { } 将JSON对象的 Key 包裹起来，否则会抛出异常。

代码：
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'b'结果：
[SQL]SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'b'[Err] 错误:  有缺陷的数组常量:"b"
LINE 1: SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'b'^
DETAIL:  数组值必须以 "{" 或者维度信息开始。

同样的，我们还可以在 #> 的基础上，继续获取这个JSON对象内的相关信息。

代码：
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'{b}'->'ba'结果：
"b1"

以上，可以看到 -> 获取的是一个JSON对象。->>是获取的一个text 文本

查看全文
如若内容造成侵权/违法违规/事实不符，请联系编程学习网邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！

银马尔科夫模型--（1）
文章目录1.定义2.隐马尔科夫模型的三个问题3.例子3.1.描述问题3.1.1.评估问题3.1.2.解码问题3.1.3.学习问题3.2.解决方案3.2.1.计算结果概率3.2.2.计算隐含状态概率 1.定义有向图模型又分为，静态贝叶斯网络和动态贝叶斯网络，动态贝叶斯网络再具体细分就是隐马尔科夫模型和…...
2024/4/16 14:18:42
HTML5系列代码：三栏布局下的页面使用CSS+DIV布局页面
div 可定义文档中的分区或节（division/section）。 div标签可以把文档分割为独立的、不同的部分。它可以用作严格的组织工具，并且不使用任何格式与其关联。如果用 id 或 class 来标记 div，那么该标签的作用会变得更加有效。 div 是一个块级元素。这意味着它的内容自动地开始…...
2024/4/20 2:55:51
个人项目开发步骤及界面
个人项目需求分析详解前言思维导图前台界面设计前言最近我在学习easyUI，就想配合之前学习的mvc框架做一个小型项目，这篇博客就讲讲项目的功能和需求分析！思维导图首先我是画了个需求分析：按照我们开发一个项目的步骤第一步，我们需要对项目进行定位，比如说这是一个什…...
2024/4/16 14:18:22
msyql数据库[云图智联]
免费学习视频欢迎关注云图智联：https://e.yuntuzhilian.com/1.数据库的基本介绍1.1数据库介绍1.1.1生活中的数据？大家在生活中随处可见可收集的非常多的信息，比如说手机上的短信、报纸上的新闻、硬盘中的文件等等，这些信息我们都可以统称为数据，那么数据一般存放在哪里呢？…...
2024/4/20 21:10:29
连连跨境支付上线eBay欧元收款服务及使用教程！
eBay是全球电子商务先锋，连接全球数以亿计的买家与卖家，通过互联网为他们创造更多的机会。不久前，eBay发布eBay2020年第一季度财报，根据财报显示，在这个前所未有的特殊时期，eBay交出了一份漂亮的成绩单：eBay第一季度营收24亿美元，总交易额213亿美元。eBay作为最有潜力的…...
2024/5/4 4:07:19
剑指offer——从尾到头打印链表
输入一个链表，按链表从尾到头的顺序返回一个ArrayList。解题思路：递归来写，非常简单 import java.util.ArrayList; public class Solution {ArrayList<Integer> list = new ArrayList<Integer>();public ArrayList<Integer> printListFromTailToHead(Li…...
2024/4/16 14:17:46
nginx 不同场景写入cookie根据if判断处理和正则匹配
if ($http_cookie !~* "access_origin_url=(.+?)(?=;|$)") {add_header Set-Cookie access_origin_url=$http_referer; domain=$root_domain; path=/;}1、正则表达式匹配：==:等值比较;~：与指定正则表达式模式匹配时返回“真”，判断匹配与否时区分字符大小写；~*…...
2024/4/19 0:49:42
NGK公链未来发展态势
区块链技术魅力在于建立信任，其核心功能是提升各个纬度的治理能力，从而解决社会问题。区块链是未来数字经济的基础设施，像今天的移动支付一样改变亿万民众的生活，从而形成从信息互联网到价值互联网的飞跃。区块链是分布式数据存储、点对点传输、共识机制、加密算法等计算机…...
2024/4/16 14:18:27
记一次zabbix-agent启动失败
启动 systemctl start zabbix-agent 报错 Job for zabbix-agent.service failed because the control process exited with error code. See "systemctl status zabbix-agent.service" and "journalctl -xe" for details.执行 journalctl -xe 显示 Jul 03 …...
2024/4/18 17:33:26
beego api请求返回base64图片
beego api请求返回base64图片代码 // @router /imgs/:img_id [get] func (t *TestController) GetImage() {img = "yourbase64"data, _ := base64.StdEncoding.DecodeString(strings.Split(img, "base64,")[1])t.Ctx.Output.Header("Content-Type&qu…...
2024/4/16 14:18:27
解决Ubuntu中文件管理器死掉的情况
有时会遇到Ubuntu文件管理器死掉的情况，怎么点击都没有反应，这时只需在终端上运行ps -A | grep nautilus，查找文件管理器nautilus对应的pid,然后sudo+kill+pid就可以关闭文件管理器进程，随便点击一个文件夹就可以重启文件管理器了...
2024/5/4 5:18:59
RFC2889广播时延测试——网络测试仪实操
一、简介RFC 2889为LAN交换设备的基准测试提供了方法学，它将RFC 2544中为网络互联设备基准测试所定义的方法学扩展到了交换设备，提供了交换机转发性能（Forwarding Performance）、拥塞控制（Congestion Control）、延迟（Latency）、地址处理（Address Handling）和错误过滤…...
2024/4/16 14:18:22
js立即执行函数的写法
<!DOCTYPE html> <html lang="en"> <head><meta charset="UTF-8"><title>Title</title> </head> <body> <ul id=”test”><li>这是第一条</li><li>这是第二条</li><li&…...
2024/5/4 8:06:06
ENOENT: no such file or directory, scandir ‘E:\workplace\xthk_teaching_management_center\node_modul
报错：解决： npm rebuild node-sass --save-dev...
2024/4/4 21:16:12
[BUUCTF 2018]Online Tool -wp
打开网页可以看到代码，进行代码审计 $sandbox = md5("glzjin". $_SERVER[REMOTE_ADDR]);echo you are in sandbox .$sandbox;@mkdir($sandbox);chdir($sandbox);获取你的ip，然后进行加密，把MD5值输出，然后以这个值创建一个目录尝试一下127.0.0.1 ，发现调用nm…...
2024/4/4 21:16:11
IDEA安装及项目创建
IDEA安装及项目创建下载地址： https://www.jetbrains.com/ 创建Java项目确保project SDK是下载的jdk位置直接下一步配置文件位置，创建文件名HELLO 新建Javaclass 输入文件名HELLO 直接就有了HELLO文件Hello,world!代码快捷键输出内容、运行、看结果...
2024/4/25 2:10:07
计算机研究生入学前的准备有哪些？
通过了研究生入学考试的磨炼，很多学生如愿收获了自己的喜悦。在确定录取以除了收获喜悦还有对于研究生生涯的憧憬，时光飞逝，研究生生涯短暂而又富有创造力，那么如何提前为短暂的研究生生涯做准备呢？1、联系导师、师兄师姐对于一些学校，复试结束后就确定了导师，导师是你的…...
2024/5/4 5:17:49
《上帝掷骰子吗量子物理史话》
超星学习通app《上帝掷骰子吗：量子物理史话》导读章节测验答案1【单选题】经典物理体系中不包括(D)。A、经典力学B、经典电磁场理论C、经典统计力学D、量子物理学2【单选题】下列各物理理论中可以划入新物理学范畴的是(D)。A、牛顿力学B、麦克斯韦电磁理论C、能量守恒定律D、量…...
2024/5/4 3:25:48
学习pytorch--》手势识别--》机器人学习
FOA，我这个是个人笔记，很乱，别看 Pytorch Linux 和 Windows 查看 CUDA 和 cuDNN 版本 https://www.cnblogs.com/wuliytTaotao/p/11453265.html#%E6%9F%A5%E7%9C%8B-cuda-%E7%89%88%E6%9C%AC Ubuntu16.04安装pytorch（pip） https://blog.csdn.net/u013832707/article/detail…...
2024/4/16 14:19:08
谷粒商城-首页商品分类
欢迎访问加群：1107019965，学习更多的知识 1. 门户首页商品分类搜索做完之后，把目光移动首页，首页最重要的模块之一便是商品分类，商品分类也是进入商品列表，找到心仪商品的另一个主要途径。 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ffx2…...
2024/4/16 14:18:17

PostgreSQL中JSON函数和操作符

一、 -> 和 ->> ：

二、 #> 和 #>> ：

相关文章

最新文章