PostgreSQL JSON 处理

1.JSON类型
    PostgreSQL支持JSON和JSONB。这两种类型在使用上几乎完全一致,主要区别是:

(1)JSON类型把输入的数据原封不动的存放到数据库中。JSONB类型在存放时把JSON解析成二进制格式。

(2)JSONB支持在其上建索引,而JSON不能,这是JSONB的一个很大的优点。

(3)JSON中会保留多余的空格,保留重复的Key,保留Key的顺序。JSONB则完全相反,不保留多余的空格,不保留重复的Key,不保留Key的顺序。

JSON类型与PostgreSQL数据库类型的映射:

JSON类型          PostgreSQL类型                        注意事项
string                  text                                             注意字符集的一些限制
number               numeric                                     JSON中没有PostgreSQL中的“NaN”,“infinity”
boolean              boolean                                     JSON仅能接受小写的“true”和“false”
null                      (none)                                       SQL中的NULL代表不同的意思

一、将结果集封装成JSON

关于如何查询返回 JSON,这里 有例子,翻译如下:

一个简单的用法就是使用 row_to_json() 函数,它接受 “行值”并返回 JSON 对象:

1
select row_to_json(tableName) from tableName;

上面查询语句返回结果类似如下:

1
{"id":6013,"text":"advancement","pronunciation":"advancement",...}

但是有时候我们只需要查询指定的列,那么我们可以使用 row() 结构函数:

select row_to_json(row(id, text)) from tableName;

上面查询语句返回了我们想要的结果,可惜丢失了列名:

1
{"f1":6013,"f2":"advancement"}

为了完善这个需求,我们必须创建一个行类型且将结果转换(cast)到这个行类型,或者使用子查询。子查询会更容易一些:

1
2
3
4
select row_to_json(t)
from (
  select id, text from tableName
AS t

上面查询语句返回了我们希望的样子:

1
{"id":6013,"text":"advancement"}

另一种常用的技术是 array_agg 和 array_to_json。array_agg 是一个聚合函数 sum 或 count。它聚集成一个 PostgreSQL 数组参数。array_to_json 以 PostgreSQL数组 拼合成一个单一的JSON值。

我们来看看 array_to_json 的用法:

1
2
3
4
select array_to_json(array_agg(row_to_json(t)))
from (
  select id, text from tableName
AS t

上面查询语句返回了一个由 JSON 对象组成的数组:

1
[{"id":6001,"text":"abaissed"},{"id":6002,"text":"abbatial"},{"id":6003,"text":"abelia"},...]

我们来一个复杂的例子(注:这个例子可能有问题):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select row_to_json(t)
from (
  select text, pronunciation,
    (
      select array_to_json(array_agg(row_to_json(d)))
      from (
        select part_of_speech, body
        from definitions
        where word_id=words.id
        order by position asc
      ) d
    as definitions
  from words
  where text = 'autumn'

上面查询语句返回结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
{
  "text""autumn",
  "pronunciation""autumn",
  "definitions": [
    {
        "part_of_speech""noun",
        "body""skilder wearifully uninfolded..."
    },
    {
        "part_of_speech""verb",
        "body""intrafissural fernbird kittly..."
    },
    {
        "part_of_speech""adverb",
        "body""infrugal lansquenet impolarizable..."
    }
  ]
}

二、json解析

一些最常见的PostgreSQL JSON运算符和处理JSON数据的函数。

JSON代表JavaScript Object Notation。JSON是一种开放的标准格式,由键值对组成。JSON的主要用途是在服务器和Web应用程序之间传输数据。与其他格式不同,JSON是人类可读的文本。

从版本9.2开始,PostgreSQL支持本机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语句将新行插入orders表中。

INSERT INTO orders (info)

VALUES

(

'{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'

);

这意味着John Doe买了6 一瓶beers

让我们同时插入多行。

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数据

要查询JSON数据,请使用该SELECT语句,该语句类似于查询其他本机数据类型:

SELECT

info

FROM

orders;

PostgreSQL JSON 处理

PostgreSQL以JSON的形式返回结果集。

PostgreSQL提供了两个本机运算符->->>可帮助您查询JSON数据。

  • 运算符->按键返回JSON对象字段。
  • 运算符->>按文本返回JSON对象字段。

以下查询使用运算符->以JSON的形式获取所有客户:

SELECT

info -> 'customer' AS customer

FROM

orders;

PostgreSQL JSON 处理

以下查询使用operator ->>以文本形式获取所有客户:

SELECT

info ->> 'customer' AS customer

FROM

orders;

PostgreSQL JSON 处理

因为->运算符返回JSON对象,所以可以使用运算符->>将其链接以检索特定节点。例如,以下语句返回所有已售出的产品:

SELECT

info -> 'items' ->> 'product' as product

FROM

orders

ORDER BY

product;

PostgreSQL JSON 处理

首先   info -> 'items'将项目作为JSON对象返回。然后info->'items'->>'product'将所有产品作为文本返回。

在WHERE子句中使用JSON运算符

我们可以使用JSON运算符in WHERE子句来过滤返回的行。例如,要找出谁购买Diaper,我们使用以下查询:

SELECT

info ->> 'customer' AS customer

FROM

orders

WHERE

info -> 'items' ->> 'product' = 'Diaper'

PostgreSQL JSON 处理

要了解谁一次购买两种产品,我们使用以下查询:

SELECT

info ->> 'customer' AS customer,

info -> 'items' ->> 'product' AS product

FROM

orders

WHERE

CAST (

info -> 'items' ->> 'qty' AS INTEGER

) = 2

PostgreSQL JSON 处理

请注意,我们使用类型转换将qty字段转换为INTEGER 类型并将其与两个进行比较。

将聚合函数应用于JSON数据

我们可以应用聚合函数MINMAXAVERAGESUM等,以JSON数据。例如,以下语句返回最小数量,最大数量,平均数量和销售产品的总数量。

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

PostgreSQL JSON 处理

PostgreSQL JSON函数

PostgreSQL为我们提供了一些帮助您处理JSON数据的函数。

json_each函数

json_each()函数允许我们将最外层的JSON对象扩展为一组键值对。请参阅以下声明:

SELECT

json_each (info)

FROM

orders;

PostgreSQL JSON 处理

如果要将一组键值对作为文本,则使用该  json_each_text()函数。

json_object_keys函数

要在最外层的JSON对象中获取一组键,请使用该  json_object_keys() 函数。以下查询返回列中嵌套items对象的所有键info

SELECT

json_object_keys (info->'items')

FROM

orders;

PostgreSQL JSON 处理

如果你想深入挖掘,还有更多的PostgreSQL JSON函数

在本教程中,我们向您展示了如何使用PostgreSQL JSON数据类型。我们向您展示了一些最重要的JSON运算符和函数,可帮助您更有效地处理JSON数据。

参考:https://www.cnblogs.com/my4piano/p/5658264.html

https://blog.csdn.net/xfg0218/article/details/83182203

上一篇:java连接https时禁用证书验证.


下一篇:Java调用HTTPS接口的证书配置