linux办公网
Postgresql中JSON数据构造与操作符实例
创始人
2024-04-09 01:02:40
0次

JSON数据构造方法

注意：区分数字与文本。

  • 这是数字：'0.1'::json
  • 这是文本：'"0.1"'::json
  • 1是数组，3是文本：'[1, "3"]'::jsonb

注意：区分数据的三种形态。

  • 这是单独数据：'1'::json
  • 这是数组里面的数据：'[1]'::json
  • 这是object里面的数据：'{"abc":1}'::json
-- 可以为字符串，必须用双引号
SELECT '"abc"'::json;			-- OKjson  
-------"abc"-- 可以为数字
SELECT '0.1'::json;				-- OKjson 
------0.1SELECT '1e100'::json;			-- OKjson  
-------1e100-- 可以为true/false/null
SELECT ' true '::json;			-- OK, even with extra whitespacejson  
--------true -- 可以为数组
SELECT '[]'::json;				-- OKjson 
------[]SELECT '[1,2]'::json;			-- OKjson  
-------[1,2]-- 可以为Objects：{key:value}
SELECT '{}'::json;				-- OKjson 
------{}SELECT '{"abc":1}'::json;		-- OKjson    
-----------{"abc":1}-- 可以嵌套
SELECT '{"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}'::json; -- OKjson                           
---------------------------------------------------------{"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}

JSON操作符与实例

  • JSON与JSONB的差别在存储上，JSON直接保存文本不做任何解析；JSONB在输入后自动解析记录二进制信息。在使用时JSONB省去了解析的步骤，使用时效率更高；JSONB支持索引；建议生产中使用JSONB。
  • 注意双引号和单引号的使用，在json中最外层使用单引号后，需要内部使用双引号。

1 适用于JSON/JSONB

操作符实例结果
->整数：选择数组中元素：输出jsonselect ‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json -> 2;{“c”:“baz”}
->整数：选择数组中元素：输出jsonselect ‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json -> -3;{“a”:“foo”}
->text：选择object中的元素：输出jsonselect ‘{“a”: {“b”:“foo”}}’::json -> ‘a’;{“b”:“foo”}
->>整数：选择数组中元素：输出textselect ‘[1,2,3]’::json ->> 2;3
->>text：选择object中的元素：输出textselect ‘{“a”:1,“b”:2}’::json ->> ‘b’;2
#>路径：按路径选择元素：输出jsonselect ‘{“a”: {“b”: [“foo”,“bar”]}}’::json #> ‘{a,b,1}’;“bar”
select ‘{“a”: {“b”: [“foo”,“bar”]}}’::json #> ‘{a,b}’;[“foo”,“bar”]
select ‘{“a”: {“b”: [“foo”,“bar”]}}’::json #> ‘{a,c}’;空：路径不通
#>>路径：按路径选择元素：输出textselect ‘{“a”: {“b”: [“foo”,“bar”]}}’::json #>> ‘{a,b,1}’;bar

（#>>多条路径只能返回最后匹配的一个）

2 适用于JSONB

子集、包含、组合

操作符实例结果
A@>B：B是A的子集？：输出boolselect ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonb;t
select ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2, “a”:1}’::jsonb;t
select ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2, “a”:2}’::jsonb;f
select ‘{“a”:1, “b”:2}’::jsonb @> ‘{}’::jsonb;t
select ‘{“a”:1, “b”:2}’::jsonb @> ‘[]’::jsonb;f
select ‘{“a”:1, “b”:2}’::jsonb @> ‘null’::jsonb;f
A<@B：A是B的子集？：输出boolselect ‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb;t
?text：顶级key或数组包含text？：输出boolselect ‘{“a”:1, “b”:2}’::jsonb ? ‘b’;t
select ‘[“a”, “b”, “c”]’::jsonb ? ‘b’;t
?|text[]：顶级key或数组包含text[]中的任意一个？：输出boolselect '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'];t
?&text[]：顶级key或数组包含text[]中的全部？：输出boolselect '["a", "b", "c"]'::jsonb ?& array['a', 'b'];t
||：组合数组：输出jsonbselect '["a", "b"]'::jsonb || '["a", "d"]'::jsonb;[“a”, “b”, “a”, “d”]
||：组合object（自动合并）：输出jsonbselect '{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb;{“a”: “b”, “c”: “d”}
select '{"a": "b"}'::jsonb || '{"a": "d"}'::jsonb;{“a”: “d”}
||：组合数组与元素：输出jsonbselect '[1, 2]'::jsonb || '3'::jsonb;[1, 2, 3]
||：组合object与元素：输出jsonbselect '{"a": "b"}'::jsonb || '42'::jsonb;[{“a”: “b”}, 42]
||：组合object与数组：输出jsonbselect '{"a": "b"}'::jsonb || '[1, 2]'::jsonb;[{“a”: “b”}, 1, 2]

删除

操作符实例结果
-text：删除key或数组元素：输出jsonbselect ‘{“a”: “b”, “c”: “d”}’::jsonb - ‘a’;{“c”: “d”}
select ‘[“a”, “b”, “c”, “b”]’::jsonb - ‘b’;[“a”, “c”]
-text[]：删除key或数组元素：输出jsonbselect ‘{“a”: “b”, “c”: “d”}’::jsonb - ‘{a,c}’::text[];{}
select ‘[“a”, “b”, “c”]’::jsonb - 0;[“b”, “c”]
select ‘[“a”, “b”, “c”]’::jsonb - 1;[“a”, “c”]
select ‘[“a”, “b”, “c”]’::jsonb - -1;[“a”, “b”]
select ‘[“a”, “b”, “c”]’::jsonb - -2;[“a”, “c”]
select ‘[“a”, “b”, “c”]’::jsonb - -3;[“b”, “c”]
总结：正数位置[0,1,2]，负数位置[-3,-2,-1]
#-text[]：按路径删除：输出jsonbselect ‘[“a”, {“b”:1}]’::jsonb #- ‘{1,b}’;[“a”, {}]

涉及jsonpath的操作符：@? jsonpath、@@ jsonpath，请先阅读下面一章：JSONPATH

操作符实例结果
@? jsonpath：给出的jsonpath是否能找到元素select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)';t
@? jsonpath：给出的jsonpath找到结果集，能否满足jsonpath中的布尔表达式？select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2';t
select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 5';f

JSONPATH

jsonpath是Postgresql12引入的特性，类似于使用XPATH的方式访问XML，参考了一些JS的语法引入的一种描述数据的语法。

例如下面一段json

{"track": {"segments": [{"location":   [ 47.763, 13.4034 ],"start time": "2018-10-14 10:05:14","HR": 73},{"location":   [ 47.706, 13.2635 ],"start time": "2018-10-14 10:39:21","HR": 135}]}
}

使用上面介绍的操作符#>> '{track,segments}'可以拿到segments数组值。

$.track.segments

select '{"track": {"segments": [{"location":   [ 47.763, 13.4034 ],"start time": "2018-10-14 10:05:14","HR": 73},{"location":   [ 47.706, 13.2635 ],"start time": "2018-10-14 10:39:21","HR": 135}]}
}'::jsonb #>> '{track,segments}';-- 结果
[{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}
]

也可以使用Jsonpath语言拿到segments数组：

select jsonb_path_query('{"track": {"segments": [{"location":   [ 47.763, 13.4034 ],"start time": "2018-10-14 10:05:14","HR": 73},{"location":   [ 47.706, 13.2635 ],"start time": "2018-10-14 10:39:21","HR": 135}]}
}'::jsonb,
'$.track.segments');-- 结果
[{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}
]

再看两个例子：

$.track.segments[*].location

select jsonb_path_query('{"track": {"segments": [{"location":   [ 47.763, 13.4034 ],"start time": "2018-10-14 10:05:14","HR": 73},{"location":   [ 47.706, 13.2635 ],"start time": "2018-10-14 10:39:21","HR": 135}]}
}'::jsonb,
'$.track.segments[*].location');-- 结果
[47.763, 13.4034]
[47.706, 13.2635]// `$.track.segments[0].location`select jsonb_path_query('{"track": {"segments": [{"location":   [ 47.763, 13.4034 ],"start time": "2018-10-14 10:05:14","HR": 73},{"location":   [ 47.706, 13.2635 ],"start time": "2018-10-14 10:39:21","HR": 135}]}
}'::jsonb,
'$.track.segments[0].location');-- 结果
[47.763, 13.4034]

$.track.segments[*].HR ? (@ > 130)

@代指.最后的元素

select jsonb_path_query('{"track": {"segments": [{"location":   [ 47.763, 13.4034 ],"start time": "2018-10-14 10:05:14","HR": 73},{"location":   [ 47.706, 13.2635 ],"start time": "2018-10-14 10:39:21","HR": 135}]}
}'::jsonb,
'$.track.segments[*].HR ? (@ > 130)');-- 结果
135

$.track.segments[*] ? (@.HR > 130)."start time"

这里@代指segments数组

select jsonb_path_query('{"track": {"segments": [{"location":   [ 47.763, 13.4034 ],"start time": "2018-10-14 10:05:14","HR": 73},{"location":   [ 47.706, 13.2635 ],"start time": "2018-10-14 10:39:21","HR": 135}]}
}'::jsonb,
'$.track.segments[*] ? (@.HR > 130)."start time"');-- 结果
"2018-10-14 10:39:21"

$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"

select jsonb_path_query('{"track": {"segments": [{"location":   [ 47.763, 13.4034 ],"start time": "2018-10-14 10:05:14","HR": 73},{"location":   [ 47.706, 13.2635 ],"start time": "2018-10-14 10:39:21","HR": 135}]}
}'::jsonb,
'$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"');-- 结果
"2018-10-14 10:39:21"
词库加载错误:未能找到文件“E:\highferrum_mysql\Configuration\Dict_Stopwords.txt”。

上一篇：8 - 复习总结java中的继承与多态

下一篇：STC51单片机29——汇编语言 取表法 流水灯

相关内容

热门资讯

银河麒麟V10SP1高级服务器... 银河麒麟高级服务器操作系统简介： 银河麒麟高级服务器操作系统V10是针对企业级关键业务...
【NI Multisim 14...   目录 序言 一、工具栏 🍊1.“标准”工具栏 🍊 2.视图工具...
AWSECS：访问外部网络时出... 如果您在AWS ECS中部署了应用程序，并且该应用程序需要访问外部网络，但是无法正常访问，可能是因为...
不能访问光猫的的管理页面 光猫是现代家庭宽带网络的重要组成部分，它可以提供高速稳定的网络连接。但是，有时候我们会遇到不能访问光...
AWSElasticBeans... 在Dockerfile中手动配置nginx反向代理。例如，在Dockerfile中添加以下代码：FR...
Android|无法访问或保存... 这个问题可能是由于权限设置不正确导致的。您需要在应用程序清单文件中添加以下代码来请求适当的权限：此外...
月入8000+的steam搬砖... 大家好，我是阿阳 今天要给大家介绍的是 steam 游戏搬砖项目，目前...
​ToDesk 远程工具安装及... 目录 前言 ToDesk 优势 ToDesk 下载安装 ToDesk 功能展示 文件传输 设备链接 ...
北信源内网安全管理卸载 北信源内网安全管理是一款网络安全管理软件，主要用于保护内网安全。在日常使用过程中，卸载该软件是一种常...
AWS管理控制台菜单和权限 要在AWS管理控制台中创建菜单和权限，您可以使用AWS Identity and Access Ma...