1、SQL+NoSQL=PostgreSQL董旭阳标普信用评级(中国)有限公司 数据库架构师SQL/JSONPostgreSQL/JSON一个案例一个反例CONTENTSQL/JSONSQL/JSONSQL标准定义了JSON数据类型和函数，支持以下JSON功能：JSON对象的存储与检索；JSON对象表示成SQL数据；SQL数据表示成JSON对象。PostgreSQL/JSONJSON数据类型PostgreSQL提供了两种JSON数据类型：JSON以及JSONB。推荐优先选择JSONB数据类型。功能JSONJSONB存储格式字符串原文存储解析后的二进制全文索引不支持支持保留空白符保留不保留保留键的顺

2、序保留不保留保留重复键保留不保留JSON函数构造函数查询函数操作函数 json_object/jsonb_object json_array jsonb_build_object jsonb_build_array to_json/to_jsonb json_objectagg json_arrayagg is not json -、-、#、#、?、?|、?&jsonb_path_query jsonb_extract_path jsonb_extract_path_text jsonb_array_elements jsonb_array_elements_text jsonb_each j

3、sonb_each_text|、-、#-jsonb_insert jsonb_set jsonb_set_lax json_strip_nulls jsonb_strip_nulls JSON函数构造函数SELECT json_object(id:1,name VALUE PostgreSQL);jsonb_object|-+id:1,name:PostgreSQL|SELECT json_array(SQL,PostgreSQL,JSON RETURNING jsonb);json_array|-+SQL,PostgreSQL,JSON|JSON函数构造函数查询函数操作函数 json_obj

4、ect/jsonb_object json_array jsonb_build_object jsonb_build_array to_json/to_jsonb json_objectagg json_arrayagg is not json -、-、#、#、?、?|、?&jsonb_path_query jsonb_extract_path jsonb_extract_path_text jsonb_array_elements jsonb_array_elements_text jsonb_each jsonb_each_text|、-、#-jsonb_insert jsonb_set

5、jsonb_set_lax json_strip_nulls jsonb_strip_nulls JSON函数查询函数SELECT id:1,name:PostgreSQL:jsonb-name;?column?|-+PostgreSQL|SELECT jsonb_path_query(a:1,3,5,7,9,$.a*?(=2&、-、#、#、?、?|、?&jsonb_path_query jsonb_extract_path jsonb_extract_path_text jsonb_array_elements jsonb_array_elements_text jsonb_each jso

6、nb_each_text|、-、#-jsonb_insert jsonb_set jsonb_set_lax json_strip_nulls jsonb_strip_nulls JSON函数操作函数SELECT id:1:jsonb|name:PostgreSQL:jsonb;?column?|-+id:1,name:PostgreSQL|SELECT jsonb_set(id:1,name:PostgreSQL,name,SQL/JSON);jsonb_set|-+id:1,name:SQL/JSON|JSON函数缺少函数 json_exists json_query json_value

7、 json_tableSELECT*FROM json_table(id:1,num:100,id:2,num:200:jsonb,$*COLUMNS(id int4 path$.id,num int4 path$.num);id|num|-+-+1|100|2|200|一个案例一个案例产品信息表CREATE TABLE product(id serial NOT NULL PRIMARY KEY,product_name VARCHAR(100)NOT NULL UNIQUE,price NUMERIC NOT NULL,create_time timestamp NOT NULL,deta

8、il JSONB);一个案例使用字符串赋值INSERT INTO product(product_name,price,create_time,detail)VALUES(iPhone 15,7000,current_timestamp,color:白色,cpu:A17,memory:256GB,size:76,160,8);SELECT*FROM product;id|product_name|price|create_time|detail|-+-+-+-+-+1|iPhone 15|7000|2023-10-29 22:00:34.866|cpu:A17,size:76,160,8,co

9、lor:白色,memory:256GB|一个案例使用构造函数赋值INSERT INTO product(product_name,price,create_time,detail)VALUES(羽绒服,500,current_timestamp,json_object(material:涤纶,weight:1.0,type:标准型,style:休闲风);SELECT*FROM product WHERE id=2;id|product_name|price|create_time|detail|-+-+-+-+-+2|羽绒服|500|2023-10-29 22:02:09.993|type:标

10、准型,style:休闲风,weight:1.0,material:涤纶|一个案例获取JSON节点元素SELECT product_name,detail-color AS color,detail-color AS color_text,detail-size-0 AS size1,detailsize0 AS size1,detail#size,0 AS size1_textFROM product WHERE id=1;product_name|color|color_text|size1|size1|size1_text|-+-+-+-+-+-+iPhone 15|白色|白色|76|76

11、|76|一个案例JSON转换为数据行SELECT id,product_name,jsonb_each(detail)FROM product;id|product_name|jsonb_each|-+-+-+1|iPhone 15|(cpu,A17)|1|iPhone 15|(size,76,160,8)|1|iPhone 15|(color,白色)|1|iPhone 15|(memory,256GB)|2|羽绒服|(type,标准型)|2|羽绒服|(style,休闲风)|.一个案例获取JSON字段中的所有KeySELECT id,product_name,jsonb_object_keys

12、(detail)FROM product;id|product_name|jsonb_object_keys|-+-+-+1|iPhone 15|cpu|1|iPhone 15|size|1|iPhone 15|color|1|iPhone 15|memory|2|羽绒服|type|2|羽绒服|style|.一个案例增加JSON元素UPDATE productSET detail=jsonb_insert(detail,system,iOS)WHERE id=1;product_name|detail|-+-+iPhone 15|cpu:A17,size:76,160,8,color:白色,m

13、emory:256GB,system:iOS|一个案例更新JSON元素UPDATE productSET detail=jsonb_set(detail,color,蓝色)WHERE id=1AND detail-color=白色;product_name|detail|-+-+iPhone 15|cpu:A17,size:76,160,8,color:蓝色,memory:256GB,system:iOS|一个案例删除JSON元素UPDATE productSET detail=detail-systemWHERE id=1;product_name|detail|-+-+iPhone 15|

14、cpu:A17,size:76,160,8,color:蓝色,memory:256GB|一个案例性能优化-创建大批量测试数据WITH RECURSIVE t AS(SELECT 1 n,产品|1 product_name,round(10000*random()price,color:棕色,height:60cm,material:实木 detailUNION ALLSELECT n+1,产品|n+1,round(10000*random()price,color:棕色,height:60cm,material:实木FROM t WHERE t.n color:蓝色;QUERY PLAN|-+

15、Gather (cost=1000.00.206817.64 rows=1 width=89)(actual time=98.989.2524.859 rows=1 loops=1)|Workers Planned:2|Workers Launched:2|-Parallel Seq Scan on product (cost=0.00.205817.54 rows=1 width=89)(actual time=1398.379.2188.557 rows=0 loops=3)|Filter:(detail color:蓝色:jsonb)|Rows Removed by Filter:333

16、3334|Planning Time:0.158 ms|Execution Time:2524.882 ms|一个案例-支持产品名称模糊查询CREATE EXTENSION IF NOT EXISTS pg_trgm;CREATE INDEX ON product USING GIN(product_name gin_trgm_ops);-支持产品属性全文搜索CREATE INDEX idx_product_detail ON product USING GIN(detail jsonb_path_ops);一个案例QUERY PLAN|-+Bitmap Heap Scan on produc

17、t (cost=2052.27.2179.36 rows=32 width=294)(actual time=0.036.0.038 rows=1 loops=1)|Recheck Cond:(product_name):text%iPhone%:text)AND(detail color:蓝色:jsonb)|Heap Blocks:exact=1|-BitmapAnd(cost=2052.27.2052.27 rows=32 width=0)(actual time=0.027.0.027 rows=0 loops=1)|-Bitmap Index Scan on product_produ

18、ct_name_idx(cost=0.00.289.48 rows=3200 width=0)(actual time=0.021.0.022 rows=1 loops=1)|Index Cond:(product_name):text%iPhone%:text)|-Bitmap Index Scan on product_detail_idx(cost=0.00.1762.52 rows=100000 width=0)(actual time=0.004.0.004 rows=1 loops=1)|Index Cond:(detail color:蓝色:jsonb)|Planning Tim

19、e:0.195 ms|Execution Time:0.087 ms|一个反例一个反例游戏玩家表CREATE TABLE game(id serial NOT NULL PRIMARY KEY,detail jsonb);CREATE TABLE player(id serial NOT NULL PRIMARY KEY,player_name varchar(100)NOT NULL UNIQUE,register_time timestamp NOT NULL,gender integer NOT NULL,game_history jsonb);问题一：“游戏名称”不是单独字段。问题二：

20、“游戏历史”不是单独表。一个反例游戏历史表CREATE TABLE game(id serial NOT NULL PRIMARY KEY,game_name varchar(100)NOT NULL UNIQUE,game_type varchar(10)NOT NULLdetail jsonb,create_time timestamp NOT NULL);CREATE TABLE player_game(id serial PRIMARY KEY,player_id integer NOT NULL,game_id integer NOT NULL,detail jsonb);一个反例查

21、询数据-玩家游戏记录SELECT p.player_name,g.game_name,.FROM(SELECT player_name,jsonb_array_elements(game_history)-gameID gameid,jsonb_array_elements(game_history)-startTime starttimeFROM player)pJOIN game g ON g.id=p.gameid:integerWHERE p.id=1AND p.gameid=1;-玩家游戏记录SELECT p.player_name,g.game_name,.FROM player

22、p JOIN player_game pg ON pg.player_id=p.idJOIN game g ON g.id=pg.game_idWHERE p.id=1AND g.id=1;问题三：使用JSON元素作为查询条件。一个反例更新数据-更新玩家游戏记录UPDATE player SET game_history=game_history|gameID:1,startTime:2023-10-29 10:00:00,endTime:2023-10-29 10:15:00,.WHERE id=1;-更新玩家游戏记录INSERT INTO player_game(player_id,gam

23、e_id,detail)VALUES(1,1,.);问题四：更新数据时需要更新整个JSON文档。参考资料FerretDB 基于PostgreSQL构建的MongoDB开源替代产品ISO/IEC 19075-6:2021-Information technology Guidance for the use of database language SQL Part 6:Support for JSONPostgreSQL:Documentation:16:8.14.JSON TypesPostgreSQL:Documentation:16:9.16.JSON Functions and OperatorsJSON:简介THANK YOU联系信息微信：buaa_dxy微信/公众号二维码