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

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

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

2 适用于JSONB

子集、包含、组合

操作符	实例	结果
`A@>B`：B是A的子集？：输出bool	select ‘{“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的子集？：输出bool	select ‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb;	t
`?text`：顶级key或数组包含text？：输出bool	select ‘{“a”:1, “b”:2}’::jsonb ? ‘b’;	t
	select ‘[“a”, “b”, “c”]’::jsonb ? ‘b’;	t
`?\|text[]`：顶级key或数组包含text[]中的任意一个？：输出bool	`select '{"a":1, "b":2, "c":3}'::jsonb ?\| array['b', 'd'];`	t
`?&text[]`：顶级key或数组包含text[]中的全部？：输出bool	`select '["a", "b", "c"]'::jsonb ?& array['a', 'b'];`	t

`\|\|`：组合数组：输出jsonb	`select '["a", "b"]'::jsonb \|\| '["a", "d"]'::jsonb;`	[“a”, “b”, “a”, “d”]
`\|\|`：组合object（自动合并）：输出jsonb	`select '{"a": "b"}'::jsonb \|\| '{"c": "d"}'::jsonb;`	{“a”: “b”, “c”: “d”}
	`select '{"a": "b"}'::jsonb \|\| '{"a": "d"}'::jsonb;`	{“a”: “d”}
`\|\|`：组合数组与元素：输出jsonb	`select '[1, 2]'::jsonb \|\| '3'::jsonb;`	[1, 2, 3]
`\|\|`：组合object与元素：输出jsonb	`select '{"a": "b"}'::jsonb \|\| '42'::jsonb;`	[{“a”: “b”}, 42]
`\|\|`：组合object与数组：输出jsonb	`select '{"a": "b"}'::jsonb \|\| '[1, 2]'::jsonb;`	[{“a”: “b”}, 1, 2]

删除

操作符	实例	结果
`-text`：删除key或数组元素：输出jsonb	select ‘{“a”: “b”, “c”: “d”}’::jsonb - ‘a’;	{“c”: “d”}
	select ‘[“a”, “b”, “c”, “b”]’::jsonb - ‘b’;	[“a”, “c”]
`-text[]`：删除key或数组元素：输出jsonb	select ‘{“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[]`：按路径删除：输出jsonb	select ‘[“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"

上一篇：聚水潭对接金蝶云星空数据方案

下一篇：关于品质和修养的名言

Postgresql中JSON数据构造与操作符实例