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)

ITPub博客

PostgreSQL DBA(183) - PG 14(Better JSON)

最新文章