PostgreSQL中查询JSON数组内指定键值（包含特定字符串）的方法

本教程详细介绍了在postgresql中，如何高效地查询json类型列中包含特定字符串的键值。文章将重点讲解如何处理json数组，通过使用`jsonb_array_elements`函数解构数组，并结合`->>`运算符精确提取键值，最后利用`like`操作符实现模糊匹配，避免了直接将整个json对象转换为文本进行模糊查询的低效和不准确性，从而实现精准和高性能的数据检索。

在现代数据库应用中，将半结构化数据存储为JSON或JSONB类型已成为一种常见实践。然而，如何高效、准确地查询这些复杂结构中的特定信息，尤其是当目标数据位于JSON数组内部并需要进行模糊匹配时，常常会带来挑战。本教程将以PostgreSQL为例，详细阐述如何解决这一问题。

1. 理解JSON/JSONB数据类型与挑战

假设我们有一个名为cyto_records的表，其中包含一个note列，其数据类型为JSONB（或JSON）。note列存储的数据结构是一个JSON对象数组，例如：

[
  {"text":"bbb","userID":"U001","time":16704,"showInReport":true},  
  {"text":"bb","userID":"U001","time":167047,"showInReport":true}
]

我们的目标是找到所有workflowid，其中note列的JSON数组中，任意一个元素的text键的值包含特定字符串（例如'bb'）。

直接将整个note列转换为文本并使用LIKE进行模糊查询（例如note::text LIKE '%bb%'）是不可取的。这种方法虽然可能在某些情况下“碰巧”得到结果，但它存在以下几个主要问题：

• 不精确性：它会在整个JSON字符串中搜索，可能匹配到键名、其他键的值或JSON结构本身，而不是我们期望的特定键的值。
• 低效性：将整个JSON对象转换为文本是一个计算密集型操作，尤其对于大型JSON数据，会严重影响查询性能，并且无法利用JSONB索引。
• 缺乏可维护性：当JSON结构发生变化时，这种查询很容易失效。

为了解决这些问题，我们需要利用PostgreSQL提供的强大JSON/JSONB函数和操作符。

2. PostgreSQL JSON/JSONB核心操作符与函数

PostgreSQL提供了一系列操作符和函数来高效处理JSON/JSONB数据。对于本场景，以下几个是关键：

• -> (JSON object field by key)：从JSON对象中提取指定键的值，结果仍为JSON类型。
• ->> (JSON object field as text by key)：从JSON对象中提取指定键的值，结果为文本类型。这是我们进行字符串比较时通常需要的。
• jsonb_array_elements(jsonb_array)：这是一个表函数，它将一个JSONB数组展开为一组JSONB值。数组中的每个元素都会作为单独的一行返回。如果您的列是JSON类型，则使用json_array_elements。
• jsonb_each_text(jsonb_object)：将JSONB对象展开为键值对的集合，其中键和值都作为文本返回。此函数不适用于数组，但了解它有助于理解JSONB处理能力。

在查询JSON数组时，jsonb_array_elements函数是核心，因为它允许我们遍历数组中的每一个对象。

3. 构建查询：分步实现

我们将通过以下步骤构建最终的SQL查询：

步骤 1: 展开JSON数组

首先，我们需要将note列中的JSON数组展开，使得数组中的每个对象都能被单独处理。这通过jsonb_array_elements函数实现。

Glean

Glean是一个专为企业团队设计的AI搜索和知识发现工具

117

查看详情

SELECT
    cr.workflowid,
    jsonb_array_elements(cr.note) AS note_element
FROM
    cyto_records cr;

执行此查询后，如果note列包含一个有两个元素的数组，那么对于该workflowid，将返回两行，每行包含数组中的一个JSON对象。

步骤 2: 提取目标键的值

从展开的每个note_element中，我们需要提取text键的值。由于我们要进行字符串比较，因此使用->>操作符将值提取为文本类型。

SELECT
    cr.workflowid,
    (jsonb_array_elements(cr.note))->>'text' AS extracted_text
FROM
    cyto_records cr;

现在，extracted_text列将包含每个数组元素中text键的文本值。

步骤 3: 应用模糊匹配条件

最后，我们对extracted_text列应用LIKE操作符进行模糊匹配，查找包含特定字符串（例如'bb'）的值。

SELECT DISTINCT
    cr.workflowid
FROM
    cyto_records cr,
    jsonb_array_elements(cr.note) AS elem -- 将jsonb_array_elements作为FROM子句的一部分
WHERE
    elem->>'text' LIKE '%bb%';

这里，我们将jsonb_array_elements(cr.note)作为FROM子句的一部分，并为其指定别名elem。这是一种更常见的处理方式，它会为cyto_records表中的每一行与jsonb_array_elements展开的每一行进行交叉连接，然后通过WHERE子句过滤出符合条件的记录。DISTINCT关键字用于确保每个workflowid只返回一次。

4. 完整示例

为了更好地演示，我们创建一个示例表并插入数据：

-- 创建示例表
CREATE TABLE cyto_records (
    recordid SERIAL PRIMARY KEY,
    workflowid INT,
    note JSONB
);

-- 插入示例数据
INSERT INTO cyto_records (workflowid, note) VALUES
(101, '[{"text":"bbb","userID":"U001","time":16704,"showInReport":true}, {"text":"bb","userID":"U001","time":167047,"showInReport":true}]'),
(102, '[{"text":"apple","userID":"U002","time":16705,"showInReport":true}, {"text":"banana","userID":"U002","time":167051,"showInReport":false}]'),
(103, '[{"text":"blueberry","userID":"U003","time":16706,"showInReport":true}]'),
(104, '[{"text":"strawberry","userID":"U004","time":16707,"showInReport":true}]');

-- 查询所有workflowid，其中note数组中任意元素的'text'键包含'bb'
SELECT DISTINCT cr.workflowid
FROM cyto_records cr,
     jsonb_array_elements(cr.note) AS elem
WHERE elem->>'text' LIKE '%bb%';

-- 预期结果:
-- workflowid
-- ----------
-- 101
-- 103

如果需要同时返回匹配的文本值，可以调整SELECT子句：

SELECT
    cr.workflowid,
    elem->>'text' AS matching_text
FROM
    cyto_records cr,
    jsonb_array_elements(cr.note) AS elem
WHERE elem->>'text' LIKE '%bb%';

-- 预期结果:
-- workflowid | matching_text
-- -----------|--------------
-- 101        | bbb
-- 101        | bb
-- 103        | blueberry

5. 注意事项与性能优化

• JSONB vs. JSON: 尽可能使用JSONB数据类型。JSONB以二进制格式存储，虽然写入时有少量开销，但查询性能远优于JSON（文本格式），因为它支持索引并且不需要在每次查询时解析整个字符串。
• 索引: 为了进一步优化查询性能，特别是对于大型表，可以考虑在note列上创建GIN索引。对于jsonb_array_elements和->>操作，一个通用的GIN索引可能无法直接加速内部元素的查询。然而，对于特定的查询模式，例如查找包含特定键或特定键值对的JSONB对象，GIN索引非常有效。
  • 例如，如果您经常查询某个键是否存在，可以使用CREATE INDEX idx_note_gin ON cyto_records USING GIN (note);
  • 对于更具体的查询，如查找数组中包含特定文本值的元素，直接对elem->>'text'进行索引是不可行的，因为它是一个动态计算的结果。但GIN索引仍然可以帮助加速@>（包含操作符）等查询。
• ILIKE用于不区分大小写的匹配: 如果需要进行不区分大小写的模糊匹配，请使用ILIKE代替LIKE。
• 空数组或NULL值: 如果note列可能包含NULL值或空数组[]，jsonb_array_elements函数会优雅地处理它们，不会产生额外的行。
• 性能考量: 尽管jsonb_array_elements非常强大，但如果JSON数组非常大（包含成千上万个元素），将其展开仍然会产生大量的中间行，可能影响查询性能。在这种极端情况下，可能需要重新考虑数据模型或采用其他技术（如应用程序层面的处理）。

总结

通过本教程，我们学习了如何在PostgreSQL中高效地查询JSONB数组内部特定键的值，并进行模糊匹配。关键在于利用jsonb_array_elements函数将JSON数组展开，然后使用->>操作符提取所需键的文本值，最后结合LIKE（或ILIKE）进行条件过滤。这种方法比直接将整个JSON列转换为文本进行搜索更为精确、高效和可维护，是处理PostgreSQL中复杂JSON数据查询的推荐实践。

以上就是PostgreSQL中查询JSON数组内指定键值（包含特定字符串）的方法的详细内容，更多请关注php中文网其它相关文章！