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=# ``` **参考资料** [Better JSON in Postgres with PostgreSQL 14](https://blog.crunchydata.com/blog/better-json-in-postgres-with-postgresql-14)