PostgreSQL（四） —— JSON Functions and Operators

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第21天，点击查看活动详情

引言

上一篇介绍了 JSON Types 数据类型，本篇介绍 JSON 数据类型用法和一些函数操作。

Java 集成 PostgreSQL

创建 Schema

CREATE TABLE IF NOT EXISTS public.json_demo
(
    id bigint NOT NULL DEFAULT nextval('json_demo_id_seq'::regclass),
    json json,
    jsonb jsonb,
    CONSTRAINT json_demo_pkey PRIMARY KEY (id)
)

集成 PostgreSQL JDBC

集成 POM

PostgreSQL JDBC(Java Database Connectivity) 定义了一些 Java 应用程序访问数据的接口。集成 POM 依赖如下。

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.3.3</version>
</dependency>

建立连接

String url = "jdbc:postgresql://localhost:5432/postgres";
String username = "postgres";
String password = "postgres";
//建立连接
Connection conn = DriverManager.getConnection(url, username, password);
//创建 statement
Statement statement = conn.createStatement();

插入 JSON 类型数据

//构造 json 对象
String jsonContent = "{\n" +
        "    "id": 1,\n" +
        "    "name": "json",\n" +
        "    "description": "hello json"\n" +
        "}";
​
//构造 jsonb 对象
String jsonbContent = "{\n" +
        "    "id": 1,\n" +
        "    "name": "jsonb",\n" +
        "    "description": "hello jsonb"\n" +
        "}";
​
//存储
PreparedStatement preparedStatement = conn.prepareStatement("INSERT INTO json_demo VALUES(?,?::JSON,?::JSONB)");
preparedStatement.setObject(1, 1);
preparedStatement.setObject(2, jsonContent);
preparedStatement.setObject(3, jsonbContent);
preparedStatement.executeUpdate();

查询 Json 对象

//执行查询语句
ResultSet resultSet = statement.executeQuery("SELECT * FROM json_demo");
//打印插入的结果
while (resultSet.next()) {
    long id = resultSet.getLong(1);
    String json = resultSet.getString(2);
    String jsonb = resultSet.getString(3);
    log.info("\nid:{},\njson:{},\njsonb:{}", id, json, jsonb);
}

查看输出

• 数据库表记录

  从数据库表记录来看，json 与 jsonb 并没有区别

  

• 控制台输出

  通过控制台的数据，可以明显看出 json 数据类型保留了输入的格式，但 jsonb 数据类型却在存储时对数据统一的处理，一般来说在后台 json 数据类型的查询对性能有损耗的，所以一般还是建议使用 jsonb。

  13:46:54.705 [main] INFO com.demo.postgresql.PgJDBC - 
  id:1,
  json:{
      "id": 1,
      "name": "json",
      "description": "hello json"
  },
  jsonb:{"id": 1, "name": "jsonb", "description": "hello jsonb"}
  

JSON Functions

官方文档对 JSON Functions and Operators 有详细的解析，这里就对 demo 数据进行一些演示

可以将 jsonb key-pair 展开

select jsonb->'id' as id, jsonb->'name' as name , jsonb ->'description' as description 
from public.json_demo;

可以进行条件查询

筛选 jsonb 数据中名称包含 jsonb 的数据

select *
from public.json_demo where jsonb->'name' @> '"jsonb"';