首页
产品
CLup:PostgreSQL高可用集群平台 CMiner: PostgreSQL中的CDC CData高性能数据库云一体机 CBackup数据库备份恢复云平台 CPDA高性能双子星数据库机 CSYun超融合虚拟机产品 ZQPool数据库连接池 ConshGuard数据保护产品
解决方案
数据库专业技术服务全栈式PostgreSQL解决方案Oracle分布式存储化数据库云PolarDB一体化解决方案
文章
客户及伙伴
中启开源
关于我们
公司简介 联系我们
中启开源

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

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

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

IS JSON

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

  1. postgres=# SELECT
  2. postgres-# pg_typeof(info),
  3. postgres-# pg_typeof(info ->> 'address')
  4. postgres-# FROM
  5. postgres-# car_info LIMIT 1;
  6. pg_typeof | pg_typeof
  7. -----------+-----------
  8. jsonb | text
  9. (1 row)

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

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

  1. postgres=# SELECT
  2. postgres-# info IS JSON,
  3. postgres-# info ->> 'address' IS JSON OBJECT
  4. postgres-# FROM
  5. postgres-# car_info LIMIT 1;
  6. ?column? | ?column?
  7. ----------+----------
  8. t | t
  9. (1 row)

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

  1. SELECT
  2. CASE
  3. WHEN
  4. info -> 'address' IS JSON ARRAY
  5. THEN
  6. (info -> 'address')[0]
  7. WHEN
  8. info -> 'address' IS JSON OBJECT
  9. THEN
  10. info -> 'address'
  11. WHEN
  12. info IS JSON SCALAR
  13. THEN
  14. info
  15. END
  16. AS primary_address
  17. FROM
  18. car_info;

更统一的JSON函数

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

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

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

同样的,还可以用JSON_OBJECTJSON_OBJECTAGG来去构建或聚合json对象:

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

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

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

  1. postgres=# SELECT
  2. postgres-# json_object('k1' value 'v1', 'k1' value 'v2' WITH UNIQUE);
  3. ERROR: duplicate JSON object key value: "k1"
  4. postgres=# SELECT
  5. postgres-# json_object('k1' value 'v1', 'k1' value 'v2');
  6. json_object
  7. ----------------------------
  8. {"k1" : "v1", "k1" : "v2"}
  9. (1 row)

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

  1. postgres=# SELECT
  2. postgres-# json_object('k1' value 'v1', 'k2' value NULL, 'k3' value 'v3' AB
  3. SENT ON NULL);
  4. json_object
  5. ----------------------------
  6. {"k1" : "v1", "k3" : "v3"}
  7. (1 row)
  8. postgres=# SELECT
  9. json_object('k1' value 'v1', 'k2' value NULL, 'k3' value 'v3' NULL ON NULL);
  10. json_object
  11. -----------------------------------------
  12. {"k1" : "v1", "k2" : null, "k3" : "v3"}
  13. (1 row)

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

  1. postgres=# SELECT
  2. postgres-# pg_typeof(json_array('v1', NULL, 'v3' RETURNING jsonb));
  3. pg_typeof
  4. -----------
  5. jsonb
  6. (1 row)
  7. postgres=# SELECT
  8. pg_typeof(json_array('v1', NULL, 'v3' RETURNING json));
  9. pg_typeof
  10. -----------
  11. json
  12. (1 row)