Skip to main content
 首页 » 数据库

PostgreSQL JSON数据类型

2022年07月19日301pander-it

PostgreSQL JSON数据类型

本文我们学习PostgreSQL JSON数据类型,同时也介绍JSON数据类型的基本操作和函数。

概述

JSON 代表 JavaScript Object Notation。JSON是开放的标准格式,由key-value对组成。JSON的主要用于在服务器与web应用之间传输数据。与其他格式不同,JSON是人类可读的文本格式。

PostgreSQL从9.2版本开始支持JSON数据类型,并提供很多函数和操作符维护json数据。下面我们创建一张新表用于说明json数据类型:

CREATE TABLE orders ( 
   ID serial NOT NULL PRIMARY KEY, 
   info json NOT NULL 
); 

orders表包括两列:

  1. id是主键,为自增列
  2. info列存储json数据

插入json数据

要插入json数据,需确保数据是格式规范的json。下面insert语句插入一行order数据:

INSERT INTO orders (info) 
VALUES 
   ( 
      '{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}' 
   ); 

表示,john Doe买了6瓶啤酒。下面同时插入多行。

INSERT INTO orders (info) 
VALUES 
   ( 
      '{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}' 
   ), 
   ( 
      '{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}' 
   ), 
   ( 
      '{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}' 
   ); 

查询json数据

使用select语句查询json数据,与其他基本数据类型类似:

SELECT 
   info 
FROM 
   orders; 

在这里插入图片描述

PostgreSQL返回一组JSON数据.PostgreSQL 默认提供了两个操作符用于查询json数据:

  • The operator -> 按键返回 JSON 对象字段.
  • The operator ->> 按文本返回JSON对象字段.

下面查询使用->操作符,查询json中所有顾客作为键:

SELECT 
   info -> 'customer' AS customer 
FROM 
   orders; 

在这里插入图片描述

下面使用->>操作获取所有顾客作为文本:

SELECT 
   info ->> 'customer' AS customer 
FROM 
   orders; 

->操作返回json对象,我们可以链式方式继续使用->>返回特定节点。举例,下面语句返回所有购买的商品:

SELECT 
   info -> 'items' ->> 'product' as product 
FROM 
   orders 
ORDER BY 
   product; 

在这里插入图片描述

首先使用info->'item’返回json对象。然后使用info->‘item’->>'product’返回所有产品文本值。

在where子句中使用json操作符

我们能在where子句中使用json操作符过滤数据行。举例,查找买了Diaper的记录:

SELECT 
   info ->> 'customer' AS customer 
FROM 
   orders 
WHERE 
   info -> 'items' ->> 'product' = 'Diaper' 

返回结果:

customer
Lily Bush

下面查询谁一次买了2个商品,语句如下:

SELECT 
   info ->> 'customer' AS customer, 
   info -> 'items' ->> 'product' AS product 
FROM 
   orders 
WHERE 
   CAST ( 
      info -> 'items' ->> 'qty' AS INTEGER 
   ) = 2 

结果如下:

customer product
Mary Clark Toy Train

注意我们使用cast转换qty字段值为integer类型,然后和2进行比较。

json 数据的聚集函数

我们能对json数据使用聚集函数,如min,max,average,sum等。举例,下面语句返回最小数量,最大数量、平均数量以及总数量。

SELECT 
   MIN ( 
      CAST ( 
         info -> 'items' ->> 'qty' AS INTEGER 
      ) 
   ), 
   MAX ( 
      CAST ( 
         info -> 'items' ->> 'qty' AS INTEGER 
      ) 
   ), 
   SUM ( 
      CAST ( 
         info -> 'items' ->> 'qty' AS INTEGER 
      ) 
   ), 
   AVG ( 
      CAST ( 
         info -> 'items' ->> 'qty' AS INTEGER 
      ) 
   ) 
  
FROM 
   orders 
min max sum avg
1 2 24 8.25

PostgreSQL JSON 函数

json_each()函数的作用是:将最外层的JSON对象展开为一组键值对。举例:

SELECT 
   json_each (info) 
FROM 
   orders; 

如果想得到一组key-value对作为文本,可以使用json_each_text()函数。

json_object_keys()函数可以获得json对象最外层的一组键。举例:

SELECT 
   json_object_keys (info->'items') 
FROM 
   orders; 

在这里插入图片描述

json_typeof函数返回json最外层key的数据类型作为字符串。可能是number, boolean, null, object, array, string。
下面语句查询所有item的数据类型:

SELECT 
   json_typeof (info->'items') 
FROM 
   orders; 

返回结果为:

json_typeof
object
object
object
object

下面语句返回嵌套类型中qty字段的数据类型:

SELECT 
   json_typeof (info->'items'->'qty') 
FROM 
   orders; 

返回结果:

json_typeof
number
number
number
number

其他函数读者可以参考官方文档。

总结

本文我们学习PostgreSQL JSON数据类型。主要通过实例展示了最常用的json操作符和函数,让我们更高效地处理json数据。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/93345799