JSON数据构造方法
注意：区分数字与文本。
- 这是数字：
'0.1'::json - 这是文本：
'"0.1"'::json - 1是数组，3是文本：
'[1, "3"]'::jsonb
注意：区分数据的三种形态。
- 这是单独数据：
'1'::json - 这是数组里面的数据：
'[1]'::json - 这是object里面的数据：
'{"abc":1}'::json
-- 可以为字符串，必须用双引号
SELECT '"abc"'::json; -- OK
json
-------
"abc"
-- 可以为数字
SELECT '0.1'::json; -- OK
json
------
0.1
SELECT '1e100'::json; -- OK
json
-------
1e100
-- 可以为true/false/null
SELECT ' true '::json; -- OK, even with extra whitespace
json
--------
true
-- 可以为数组
SELECT '[]'::json; -- OK
json
------
[]
SELECT '[1,2]'::json; -- OK
json
-------
[1,2]
-- 可以为Objects：{key:value}
SELECT '{}'::json; -- OK
json
------
{}
SELECT '{"abc":1}'::json; -- OK
json
-----------
{"abc":1}
-- 可以嵌套
SELECT '{"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}'::json; -- OK
json
---------------------------------------------------------
{"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
代码语言：javascript复制{
"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
代码语言：javascript复制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数组：
代码语言：javascript复制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
代码语言：javascript复制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)
@代指.最后的元素
代码语言：javascript复制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数组
代码语言：javascript复制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"
代码语言：javascript复制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"
