PostgreSQL JSON查询

今天药忘吃喽~ 2023-07-20 13:31 16阅读 0赞

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表包括两列：  
id是主键，为自增列  
info列存储json数据

## **1.插入json数据** ##

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

INSERT INTO orders (info)
    VALUES
       (
          '{ "customer": "John Doe", "items": {"product": "Beer","qty": 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}}'
       );

## **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’返回所有产品文本值。**

## **3.在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进行比较。

## **4.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

## **5.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字段的数据类型：