PostgreSQL 中的 JSON：彻底改变数据库中的数据灵活性

在这篇文章中，我们将介绍 postgresql 对 json 对象的实现和处理方法。拥有一些 linux、postgres 和 json 方面的经验是必要的，因为我们不仅要介绍这些新功能，还要介绍如何实现它们。

本文使用在 ubuntu 23.04 上运行的 postgresql 16（开发版）编写。首先，我将简要回顾一下 json 的背景知识，然后继续介绍我们如何在 postgres 中使用 json，最后介绍我们可以使用哪些有用的函数来与 json 对象进行交互。

 

#1 背景

javascript 对象表示法（json）是一种开放的标准文件格式，用于将信息存储在键值对中。它拥有一种轻量级且独立于语言的格式，既易于大家阅读，又易于机器生成和解析。它的主要优势以及为什么它在数据存储中变得如此普遍，是因为它在应用程序之间的无缝互操作性。这很适合 web 应用程序，因为我们通常需要两个程序进行通信，并且每个程序可能使用不同的语言来实现。只要每个程序都有解析json文件的方法，无论另一个程序使用什么软件或硬件，它们都应该能够有效地进行通信。现在我们看到了 json 在数据存储方面有多棒，让我们看看如何将其作为 ou postgres 数据库的一部分。

 

#2 使用json

postgresql 有两种用于在表中存储 json 数据的数据类型，分别是json 和 jsonb。json 类型将 json 数据存储为字符串，因此在读回时，接收应用程序需要将文本转换回 json 对象。另一方面，jsonb 类型将 json 对象直接存储为其二进制表示形式。当我们将 json 对象存储为 jsonb 时，postgresql 会将 json 类型映射到它自己的数据类型，这些数据类型来自 postgresql 文档：

这两种类型都接受几乎相同的输入。但是，由于其效率，大多数应用程序将从使用 jsonb 中受益更多。因此，我们的示例将主要侧重于使用 jsonb。

要开始在 postgres 中使用 json，我们首先必须创建一个表，其中包含类型为 json 的列：

# create table t1 (id int, data jsonb);

现在我们可以插入一些数据：

# insert into t1 values (1, '{"a":1, "b":"hello", "c":{"d":"world","e":2},"arr":[1,2,3]}');

让我们看看这些数据是如何表示的：

# select * from t1;
 id |                     data                      
----+-----------------------------------------------
  1 | {"a":1, "b":"hello", "c":{"d":"world","e":2},"arr":[1,2,3]}
(1 row)

postgresql 不仅存储 json 对象，它还具有自己的函数，可用于与使用键、值对作为查询中的参数进行交互。让我们来看看如何做到这一点。

 

json functions

#3 运营商

postgresql 实现了用于从 json 对象访问元素的运算符。postgresql 文档中总结了这些运算符：

使用这些运算符，我们可以从之前插入的 json 对象中访问元素。这些运算符返回的值如下所示：

# select data->'a' as result from t1;
 result 
--------
 1
(1 row)

postgres=# select data->'arr'->2 as result from t1;
 result 
--------
 3
(1 row)

现在我们可以访问这些值，我们可以使用它们来查询表中的行。

# insert into t1 values (1,'{"num":12,"arr":[1,2,3]}'),(2,'{"num":14,"arr":[4,5,6]}'),(3,'{"num":16,"arr":[7,8,9]}');
# select data from t1 where (data->'arr'->1)::integer >= 5;
          result          
--------------------------
 {"num":14,"arr":[4,5,6]}
 {"num":16,"arr":[7,8,9]}
(2 rows)

正如我们所看到的，只选择了“arr”键中第 2 个元素大于或等于 5 的行。

 

#4 下标

这些 json 对象还支持在 postgres 中下标，就像在许多编程语言中一样。我们可以将上面的运算符转换为下标，如下所示：

# select data from t1 where (data['arr'][1])::integer >= 5;
             data              
-------------------------------
 {"arr": [4, 5, 6], "num": 14}
 {"arr": [7, 8, 9], "num": 16}
(2 rows)

和以前一样，我们也可以在 select 语句中使用下标：

# select data['num'] from t1 where (data['arr'][1])::integer >= 5;
 data 
------
 14
 16
(2 rows)

对于有 json 经验的人来说，这种语法可能更熟悉。随意使用其中任何一个，因为它们的功能非常相似，接受键的文本输入和数组的整数索引。

 

#5 功能

postgresql 还实现了更强大的函数，用于转换和检索 json 对象的大小、键和迭代器等信息。当然，和前面一样，所有这些函数都可以在查询内部使用，从而使 json 对象在数据库中更加强大。我们将在json函数示例中使用以下表模式和数据:

# create table myjson (id int, data jsonb);
# insert into myjson values(1,'{"mynum":1,"mytext":"hello","myarr":[1,2,3,4,5]}');

更多函数可以在 postgresql 文档的表 9.41 中找到。我们将在这里简要介绍一些更常见的函数的子集。

array_to_json

将任何 sql 值转换为 json 二进制类型。

select to_jsonb (data['myarr']) from myjson;
    to_jsonb     
-----------------
 [1, 2, 3, 4, 5]
(1 row)

jsonb_array_length

返回 json 二进制数组中的元素数。

select jsonb_array_length (data['myarr']) from myjson;
 jsonb_array_length 
--------------------
                  5
(1 row)

jsonb_each

将顶级 json 对象转换为键值对。

select jsonb_each (data) from myjson;
        jsonb_each         
---------------------------
 (myarr,"[1, 2, 3, 4, 5]")
 (mynum,1)
 (mytext,"""hello""")

jsonb_object_keys

返回 json binary 对象的键。

select jsonb_object_keys (data) from myjson;
 jsonb_object_keys 
-------------------
 myarr
 mynum
 mytext
(3 rows)

 

#6 结论

在这篇博客中，我们了解了 postgresql json 数据类型以及如何使用它来存储、访问和管理 json 对象。首先，我们简要介绍了 json 格式的背景及其在 web 上的实用性。然后，我们研究了如何设置一个表来使用 json 数据类型，然后是我们可以访问它们的不同方法。最后，我们查看了 json 对象将有权访问的函数的一小部分，以及如何在实现查询中使用它们。

json 数据类型是一个非常灵活且可互操作的对象，可被大量 web api 接口理解。如果您的数据库与任何类型的 web 应用程序交互，请考虑如何使用 json 来简化应用程序之间的数据传递。

 

*引用：

[1]“8.14. json types.” postgresql documentation, the postgresql global development group, 9 nov. 2023, www.postgresql.org/docs/current/datatype-json.html.

[2]“json functions and operators.” postgresql documentation, the postgresql global development group, 8 nov. 2018, www.postgresql.org/docs/9.3/functions-json.html.

[3]“postgresql json.” postgresql json tutorial, postgresql tutorial, www.postgresqltutorial.com/postgresql-tutorial/postgresql-json/. accessed 12 jan. 2024.