数据库提示 (Database Tips)
JSONB Array of Objects Into Rows Using the jsonb_to_recordset() function
使用jsonb_to_recordset()函数将JSONB对象数组划分为行
PostgreSQL is an awesome database, with an awesome data type, JSON. It actually has two JSON data types, json and jsonb.
PostgreSQL是一个很棒的数据库，具有很棒的数据类型JSON。 实际上，它具有两种JSON数据类型：json和jsonb。
JSON data types are for storing JSON (JavaScript Object Notation) data.
JSON数据类型用于存储JSON(JavaScript对象表示法)数据。
There are two JSON data types: json and jsonb. They accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.
有两种JSON数据类型：json和jsonb。 他们接受几乎相同的值集作为输入。 实际的主要区别是效率之一。 json数据类型存储输入文本的精确副本，处理函数必须在每次执行时重新解析； jsonb数据以分解后的二进制格式存储，由于增加了转换开销，因此输入速度稍慢，但由于不需要解析，因此处理速度明显更快。 jsonb还支持索引编制，这是一个很大的优势。
PostgreSQL is rich in functions and operators for querying this data type.
PostgreSQL有丰富的函数和运算符可用于查询此数据类型。
我们的目标 (Our Goal)
In this article we will focus on one function in particular:
在本文中，我们将特别关注一个功能：
jsonb_to_recordset(jsonb data).
jsonb_to_recordset(jsonb数据)。
This function is particularly useful when you have an Array of Objects and want to query against the Values of one or more Keys using standard comparison operators including wildcards.
当您有一个对象数组并想使用标准比较运算符(包括通配符)查询一个或多个键的值时，此功能特别有用。
For example, we may have jsonb data like this, showing products purchased by a customer.
例如 ，我们可能有这样的jsonb数据，显示客户购买的产品。
[{
"productid": "3",
"name": "Virtual Keyboard",
"price": "150.00"}, {
"productid": "1",
"name": "Dell 123 Laptop Computer",
"price": "1300.00"},
{
"productid": "8",
"name": "LG Ultrawide Monitor",
"price": "190.00"}
]If we have several customers and their purchases stored in a jsonb column, we may want to know things like,
如果我们有几个客户并将他们的购买存储在jsonb列中，我们可能想知道诸如此类的信息，
Who bought Virtual Keyboards (Where name = ‘Virtual Keyboard’)
谁购买了虚拟键盘( 名称为“虚拟键盘” )
Who bought any kind of Keyboard (Where name Like ‘%Keyboard)
谁购买了任何种类的键盘( 名称如'％Keyboard )
Who bought a Laptop and Keyboard (Where name Like ‘%Laptop%’ or name Like ‘%Keyboard’)
谁购买了笔记本电脑和键盘( 名称如“％Laptop％”或名称如“％Keyboard” )
- How many of each product (name) has been purchased. 每个产品(名称)已购买多少。
and many others, of course.
当然还有很多其他
假设条件 (Assumptions)
I will assume you have PostgreSQL and something along the lines of pgAdmin.
我将假设您拥有PostgreSQL和pgAdmin之类的东西。
让我们开始吧 (Let’s Get Started)
建立我们的数据 (Building Our Data)
Open pgAdmin and create a database as desired.
打开pgAdmin并根据需要创建数据库。
Right-Click on the database name and choose Query Tool.
右键单击数据库名称，然后选择“ 查询工具” 。
Run the snippet below to create a simple table that will have an id, purchaser name and a jsonb column that stores an array of json objects, which will store items purchased.
运行下面的代码片段以创建一个简单的表，该表将具有一个id，购买者名称和一个jsonb列，该列存储json对象数组，该数组将存储购买的商品。
CREATE TABLE public.purchases
(
id serial PRIMARY KEY,
purchaser varchar(50),
items_purchased jsonb
);4. Run the snippet below to insert four records in to the table.
4.运行下面的代码片段，将四个记录插入到表中。
INSERT INTO purchases (purchaser,items_purchased) VALUES ('Bob','[{
"productid": "1",
"name": "Dell 123 Laptop Computer",
"price": "1300.00"},
{
"productid": "2",
"name": "Mechanical Keyboard",
"price": "120.00"}
]');INSERT INTO purchases (purchaser,items_purchased) VALUES ('Carol','[{
"productid": "3",
"name": "Virtual Keyboard",
"price": "150.00"}, {
"productid": "1",
"name": "Dell 123 Laptop Computer",
"price": "1300.00"},
{
"productid": "8",
"name": "LG Ultrawide Monitor",
"price": "190.00"}
]');INSERT INTO purchases (purchaser,items_purchased) VALUES ('Ted','[{
"productid": "6",
"name": "Ergonomic Keyboard",
"price": "90.00"},
{
"productid": "7",
"name": "Dell 789 Desktop Computer",
"price": "120.00"}
]');INSERT INTO purchases (purchaser,items_purchased) VALUES ('Alice','[{
"productid": "7",
"name": "Dell 789 Desktop Computer",
"price": "120.00"},
{
"productid": "2",
"name": "Mechanical Keyboard",
"price": "120.00"}
]');5. Run the snippet below to see,
5.运行下面的代码片段，
select * from purchases;
我们的职能 (Our Function)
了解jsonb_to_recordset(jsonb数据) (Understanding jsonb_to_recordset(jsonb data))
Builds an arbitrary set of records from a JSON array of objects (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause.
根据对象的JSON数组构建任意记录集(请参见下面的注释)。 与所有返回记录的函数一样，调用者必须使用AS子句显式定义记录的结构。
Note:
注意：
Extracted key values can then be referenced in other parts of the query, like WHERE clauses.
然后可以在查询的其他部分(例如WHERE子句)中引用提取的键值。
What does “the caller must explicitly define the structure of the record with an AS clause.” mean?
“ 调用程序必须做什么” 必须使用AS子句明确定义记录的结构。 “ 意思？
This means basically that we are defining the structure of our recordset , the columns and their data type. Similar to defining a relational table structure.
这基本上意味着我们正在定义记录集的结构， 列及其数据类型 。 类似于定义关系表结构。
Examine the following code.
检查以下代码。
select * from purchases,jsonb_to_recordset(purchases.items_purchased) as items(name text);- In the FROM clause we reference the table and the jsonb column, purchases.items, which is passed to the jsonb_to_recordset() function. It returns a set of Key/Value pairs that we must define the storage for. 在FROM子句中，我们引用表和jsonb列purchases.items，该列传递给jsonb_to_recordset()函数。 它返回一组我们必须为其定义存储的键/值对。
In the AS clause we choose an arbitrary name, items, for our recordset structure, the storage of the Key/Values.
在AS子句中，我们为记录集结构选择键/值的存储的任意名称，项目。
- In parentheses would be a comma separated list of the Keys we want (in this case just name) and its data type, text. 括号中是我们想要的键(在本例中为名称)及其数据类型，文本的逗号分隔列表。
For each Key, a new column is created when the query is run.
对于每个键，在运行查询时都会创建一个新列 。
- Since a new column is created, we can reference it in the Select, Where, Group By and Order by clauses. 由于创建了新列，因此我们可以在Select，Where，Group By和Order by子句中引用它。
Run the code above to get,
运行上面的代码以获取，
Try this snippet to create two columns.
尝试使用此代码段创建两列。
select * from purchases,jsonb_to_recordset(purchases.items_purchased) as items(name text, price text);
使用我们的新专栏 (Using Our New Columns)
Now that we have the ability to create columns from our jsonb Keys, we can reference them in Select, Where, Group By and Order By clauses.
现在我们已经能够从jsonb键创建列，我们可以在Select，Where，Group By和Order By子句中引用它们。
尝试以下每个 (Try each of the following)
- Using the column in Select and Where 在选择和位置中使用列
select purchaser,items.name from purchases,jsonb_to_recordset(purchases.items_purchased) as items(name text)
where items.name like '%Monitor';
2. Using the column in Select and Where
2.使用选择和位置中的列
select purchaser,items.name, items.price from purchases,jsonb_to_recordset(purchases.items_purchased) as items(name text, price text)
where TO_NUMBER(items.price,'9999')>=1000Note: Because our prices are text, we needed to convert to a number. More on TO_NUMBER() can be found here.
注意 ：因为我们的价格是文本，所以我们需要转换为数字。 有关TO_NUMBER()的更多信息，请参见此处 。
3. Using a column in a Group By
3.在分组依据中使用列
select items.name, count(*) as num from purchases,jsonb_to_recordset(purchases.items_purchased) as items(name text)
group by items.name
order by num Desc
结论 (Conclusion)
As mentioned before, this is not the only JSON function. There are many functions and operators for JSON data.
如前所述，这不是唯一的JSON函数。 JSON数据有很多功能和运算符。
I am exploring them everyday as I built a RESTful API for a PERN stack.
在为PERN堆栈构建RESTful API时，我每天都在探索它们。
If you found this useful, start exploring json, jsonb and the other operators and functions for JSON as well as the regular SQL operations available in PostgreSQL.
如果您发现这很有用，请开始探索json，jsonb和JSON的其他运算符和函数，以及PostgreSQL中可用的常规SQL操作。
Thank you for reading and coding along!
感谢您的阅读和编码！
You may also enjoy,
您可能还会喜欢，
4849
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
