PostgreSQL 14对JSON数据的处理增强了易用性,可使用操作符”[]”而不是以前蹩脚的”->、->>”来访问JSON数据.
PG 13
testdb=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
testdb=# drop table if exists t_json;
DROP TABLE
testdb=# create table t_json(details jsonb);
CREATE TABLE
testdb=# select jsonb_pretty('{"key1":"value1","key2":{"l2":"level2","l3":{"l31":"level3"}}}');
jsonb_pretty
-----------------------------
{ +
"key1": "value1", +
"key2": { +
"l2": "level2", +
"l3": { +
"l31": "level3"+
} +
} +
}
(1 row)
testdb=# insert into t_json values('{"key1":"value1","key2":{"l2":"level2","l3":{"l31":"level3"}}}');
INSERT 0 1
testdb=# select * from t_json where details->'key2'->>'l2' = 'level2';
details
-----------------------------------------------------------------------
{"key1": "value1", "key2": {"l2": "level2", "l3": {"l31": "level3"}}}
(1 row)
testdb=# select * from t_json where details['key2']['l2'] = 'level2';
2021-06-05 11:46:17.015 CST [2758] ERROR: cannot subscript type jsonb because it is not an array
2021-06-05 11:46:17.015 CST [2758] STATEMENT: select * from t_json where details['key2']['l2'] = 'level2';
ERROR: cannot subscript type jsonb because it is not an array
testdb=# select * from t_json where details['key2']['l2'] = '"level2"';
2021-06-05 11:46:17.016 CST [2758] ERROR: cannot subscript type jsonb because it is not an array
2021-06-05 11:46:17.016 CST [2758] STATEMENT: select * from t_json where details['key2']['l2'] = '"level2"';
ERROR: cannot subscript type jsonb because it is not an array
testdb=#
PG 14
[local]:5014 pgmaster@testdb=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 14beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
Time: 0.435 ms
[local]:5014 pgmaster@testdb=# drop table if exists t_json;
DROP TABLE
Time: 11.813 ms
[local]:5014 pgmaster@testdb=# create table t_json(details jsonb);
CREATE TABLE
Time: 9.772 ms
[local]:5014 pgmaster@testdb=# select jsonb_pretty('{"key1":"value1","key2":{"l2":"level2","l3":{"l31":"level3"}}}');
jsonb_pretty
-----------------------------
{ +
"key1": "value1", +
"key2": { +
"l2": "level2", +
"l3": { +
"l31": "level3"+
} +
} +
}
(1 row)
Time: 0.705 ms
[local]:5014 pgmaster@testdb=# insert into t_json values('{"key1":"value1","key2":{"l2":"level2","l3":{"l31":"level3"}}}');
INSERT 0 1
Time: 3.618 ms
[local]:5014 pgmaster@testdb=# select * from t_json where details->'key2'->>'l2' = 'level2';
details
-----------------------------------------------------------------------
{"key1": "value1", "key2": {"l2": "level2", "l3": {"l31": "level3"}}}
(1 row)
Time: 0.619 ms
[local]:5014 pgmaster@testdb=# select * from t_json where details['key2']['l2'] = 'level2';
2021-06-05 11:46:03.014 CST [2849] ERROR: invalid input syntax for type json at character 52
2021-06-05 11:46:03.014 CST [2849] DETAIL: Token "level2" is invalid.
2021-06-05 11:46:03.014 CST [2849] CONTEXT: JSON data, line 1: level2
2021-06-05 11:46:03.014 CST [2849] STATEMENT: select * from t_json where details['key2']['l2'] = 'level2';
ERROR: invalid input syntax for type json
LINE 1: select * from t_json where details['key2']['l2'] = 'level2';
^
DETAIL: Token "level2" is invalid.
CONTEXT: JSON data, line 1: level2
Time: 0.253 ms
[local]:5014 pgmaster@testdb=# select * from t_json where details['key2']['l2'] = '"level2"';
details
-----------------------------------------------------------------------
{"key1": "value1", "key2": {"l2": "level2", "l3": {"l31": "level3"}}}
(1 row)
Time: 0.650 ms
[local]:5014 pgmaster@testdb=#