文章：PostgreSQL 16 JSON新特性

很多小伙伴对PostgreSQL 16有哪些改进很感兴趣，今天就给大家分享一下PostgreSQL 16关于JSON的一些新特性，帮助大家更好的使用PostgreSQL 16。

使用下面的SQL创建测试表，后面所有的演示内容均使用下面的表和数据：

CREATE TABLE car_info (id serial PRIMARY KEY, owner text NOT NULL, info jsonb );
INSERT INTO car_info (owner, info)
VALUES
('王伟', '{"age": 7, "price": 210000, "color": ["RED", "BLUE"], "address": {"province": "安徽", "city": "合肥"}}'),
('李强', '{"age": 2, "price": 98000, "color": ["ORANGE", "BLUE"], "address": {"province": "浙江", "city": "嘉兴"}}'),
('刘明天', '{"age": 3, "price": 530000, "color": ["BLACK", "WHITE"], "address": {"province": "黑龙江", "city": "哈尔滨"}}'),
('付七七', '{"age": 11, "price": 1010000, "color": ["RED", "BLACK"], "address": {"province": "四川", "city": "成都"}}')
;

IS JSON

在PostgreSQL 16之前，如果想要测试一个字段是不是JSON，通常会使用pg_typeof：

postgres=# SELECT
postgres-# pg_typeof(info),
postgres-# pg_typeof(info ->> 'address')
postgres-# FROM
postgres-# car_info LIMIT 1;
 pg_typeof | pg_typeof
-----------+-----------
 jsonb     | text
(1 row)

可以看到，上面的SQL查询里，info这个字段返回的是jsonb类型，而查询info这个json字段内部的字段，则只能显示text。

在PostgreSQL 16后，可以使用IS JSON [OBJECT,ARRAY,SCALAR]来去测试一个字段是不是JSON，SQL如下所示：

postgres=# SELECT
postgres-# info IS JSON,
postgres-# info ->> 'address' IS JSON OBJECT
postgres-# FROM
postgres-# car_info LIMIT 1;
 ?column? | ?column?
----------+----------
 t        | t
(1 row)

可以看到，IS JSON和IS JSON OBJECT都会返回true，这个特性在我们面临json字段格式不一致时，特别有用，比如使用下面的SQL可以很好的处理同一个JSON字段不同数据类型的情况：

SELECT
  CASE
    WHEN
      info -> 'address' IS JSON ARRAY
    THEN
(info -> 'address')[0]
    WHEN
      info -> 'address' IS JSON OBJECT
    THEN
      info -> 'address'
    WHEN
      info IS JSON SCALAR
    THEN
      info
  END
  AS primary_address
FROM
  car_info;

更统一的JSON函数

PostgreSQL数据库实现了json和jsonb两种存储json的数据类型，而SQL并没有实现这个，所以我们需要使用以jsonb_或json_开头的函数来去构建json对象。

而在PG 16之后，我们可以使用json_array和json_arrayagg来去完成：

postgres=# SELECT
postgres-# json_array(owner, info ->> 'age')
postgres-# FROM
postgres-# car_info;
    json_array
------------------
 ["王伟", "7"]
 ["李强", "2"]
 ["刘明天", "3"]
 ["付七七", "11"]
(4 rows)

postgres=# SELECT
postgres-# ((info ->> 'age')::integer / 10) * 10 AS AGE_GROUP,
postgres-# json_arrayagg(owner)
postgres-# FROM
postgres-# car_info
postgres-# GROUP BY 1;
 age_group |       json_arrayagg
-----------+----------------------------
         0 | ["王伟", "李强", "刘明天"]
        10 | ["付七七"]
(2 rows)

同样的，还可以用JSON_OBJECT和JSON_OBJECTAGG来去构建或聚合json对象：

postgres=# SELECT
postgres-# json_object('owner' value owner, 'age': info ->> 'age')
postgres-# FROM
postgres-# car_info;
            json_object
------------------------------------
 {"owner" : "王伟", "age" : "7"}
 {"owner" : "李强", "age" : "2"}
 {"owner" : "刘明天", "age" : "3"}
 {"owner" : "付七七", "age" : "11"}
(4 rows)

postgres=# SELECT
postgres-# ((info ->> 'age')::integer / 10) * 10 AS AGE_GROUP,
postgres-# json_objectagg(owner value info ->> 'age')
postgres-# FROM car_info
postgres-# GROUP BY 1;
 age_group |                 json_objectagg
-----------+------------------------------------------------
         0 | { "王伟" : "7", "李强" : "2", "刘明天" : "3" }
        10 | { "付七七" : "11" }
(2 rows)

可以通过指定关键字来修改行为

通过UNIQUE关键字来指定json的键值必须唯一，如果不唯一，则报错

postgres=# SELECT
postgres-# json_object('k1' value 'v1', 'k1' value 'v2' WITH UNIQUE);
ERROR:  duplicate JSON object key value: "k1"
postgres=# SELECT
postgres-# json_object('k1' value 'v1', 'k1' value 'v2');
        json_object
----------------------------
 {"k1" : "v1", "k1" : "v2"}
(1 row)

使用ABSENT ON NULL去忽略值是NULL的字段：

postgres=# SELECT
postgres-# json_object('k1' value 'v1', 'k2' value NULL, 'k3' value 'v3' AB
SENT ON NULL);
        json_object
----------------------------
 {"k1" : "v1", "k3" : "v3"}
(1 row)
postgres=# SELECT
json_object('k1' value 'v1', 'k2' value NULL, 'k3' value 'v3' NULL ON NULL);
               json_object
-----------------------------------------
 {"k1" : "v1", "k2" : null, "k3" : "v3"}
(1 row)

使用RETURN关键字来控制返回的值是JSON还是JSONB

postgres=# SELECT
postgres-# pg_typeof(json_array('v1', NULL, 'v3' RETURNING jsonb));
 pg_typeof
-----------
 jsonb
(1 row)
postgres=# SELECT
pg_typeof(json_array('v1', NULL, 'v3' RETURNING json));
 pg_typeof
-----------
 json
(1 row)