在PostgreSQL中查询JSON数组内指定键值对的技巧

本文详细介绍了如何在PostgreSQL数据库中高效查询JSON类型列中的数组数据。针对包含多个JSON对象的数组，我们将学习如何提取特定键的值，并根据这些值进行过滤和查找，从而解决直接将整个JSON转换为文本进行模糊匹配可能导致的错误结果。文章将提供清晰的步骤、PostgreSQL特有的JSON函数示例代码，并强调使用jsonb类型及相关操作符的最佳实践。

在现代数据库应用中，JSON（JavaScript Object Notation）数据类型因其灵活性而被广泛使用。PostgreSQL提供了强大的JSON和JSONB数据类型及其丰富的操作符和函数，使得在数据库层面处理结构化或半结构化数据变得高效。本教程将专注于解决一个常见场景：如何从存储在JSON数组中的对象中，根据特定键的值进行精确或模糊查询。

问题场景描述

假设我们有一个名为cyto_records的表，其中包含一个note列，其数据类型为json（或更推荐的jsonb）。该note列存储的数据是一个JSON数组，每个数组元素都是一个JSON对象，例如：

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

我们的目标是找出workflowid，其对应的note列中，某个JSON对象内的text键的值包含特定字符串（例如'bb'）。

直接将整个note列转换为文本并使用LIKE进行匹配（如rr.interval_note::text LIKE '%aaa%'）是不可取的。这种方法虽然可能在某些情况下“凑效”，但它会将整个JSON结构扁平化为字符串，导致匹配不准确。例如，如果某个userID或time字段的值也包含'bb'，那么即便text字段不包含，也会被错误地匹配到。我们需要一种能够精确到JSON对象内部特定键的查询方法。

PostgreSQL中的JSON/JSONB查询策略

PostgreSQL提供了专门的函数和操作符来处理JSON和JSONB数据。对于查询JSON数组中的元素，关键在于以下几点：

1. jsonb_array_elements() 函数： 这个函数可以将一个JSONB数组展开成一组单独的JSONB对象，每个对象作为一行返回。这是处理数组内元素的基础。
2. ->> 操作符： 用于从JSON对象中提取指定键的值，并将其作为文本字符串返回。
3. EXISTS 子查询： 当我们需要检查是否存在至少一个符合条件的数组元素时，EXISTS子查询是一个高效的选择。

解决方案步骤与示例

我们将通过以下步骤构建一个准确的PostgreSQL查询：

1. 将JSON列转换为JSONB (推荐)

尽管原始列可能是json类型，但PostgreSQL的jsonb类型在存储效率和查询性能上通常优于json类型，尤其是在需要频繁查询或修改JSON数据时。如果你的note列是json类型，建议在查询中将其显式转换为jsonb，或者考虑将列类型直接更改为jsonb。

-- 如果note列是json类型，可以在查询中转换
r.note::jsonb

2. 展开JSON数组元素

使用jsonb_array_elements()函数将note列中的JSON数组展开。这通常在FROM子句中通过LATERAL JOIN或在EXISTS子查询中完成。

Glean

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

117

查看详情

SELECT 1
FROM jsonb_array_elements(r.note::jsonb) AS elem
-- 'elem' 现在代表了数组中的每一个独立的JSON对象

3. 提取指定键的值

从展开的每个JSON对象elem中，使用->>操作符提取text键的值。

(elem->>'text')
-- 这将返回'text'键对应的值，例如'bbb'或'bb'，作为文本字符串

4. 应用字符串匹配条件

对提取出的文本值应用LIKE（或ILIKE进行不区分大小写的匹配）操作符进行模糊匹配。

WHERE (elem->>'text') LIKE '%bb%'

5. 整合为完整查询

将上述步骤整合到一个EXISTS子查询中，以判断是否存在至少一个满足条件的数组元素。

SELECT DISTINCT r.workflowid
FROM cyto_records r
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(r.note::jsonb) AS elem
    WHERE (elem->>'text') LIKE '%bb%'
);

代码解释：

• SELECT DISTINCT r.workflowid: 选择不重复的workflowid。
• FROM cyto_records r: 从cyto_records表中查询。
• WHERE EXISTS (...): 使用EXISTS子查询来检查是否存在满足条件的行。
• jsonb_array_elements(r.note::jsonb) AS elem: 将r.note列（先转换为jsonb）中的JSON数组展开，每个元素作为elem返回。
• (elem->>'text') LIKE '%bb%': 在展开的每个elem对象中，提取text键的值（作为文本），然后检查它是否包含子字符串'bb'。

这个查询将准确地找到那些note列中至少有一个对象其text字段包含'bb'的workflowid。

注意事项与最佳实践

1. JSON vs. JSONB： 强烈建议使用jsonb类型存储JSON数据。jsonb以二进制格式存储，查询效率更高，支持索引，并且提供了更丰富的操作符。如果你的列是json类型，考虑将其转换为jsonb以提升性能。
2. 索引： 对于频繁查询JSONB列中的特定键，可以考虑创建GIN索引。例如，如果你经常查询text字段，可以创建一个表达式索引：

   CREATE INDEX idx_cyto_records_note_text_gin ON cyto_records USING GIN ((note->'text'));

   登录后复制

   或者，如果需要更复杂的路径查询，可以使用jsonb_path_ops操作符类：

   CREATE INDEX idx_cyto_records_note_gin ON cyto_records USING GIN (note jsonb_path_ops);

   登录后复制

   请注意，LIKE操作符通常不能直接利用此类索引，但EXISTS与jsonb_array_elements结合可能会受益于jsonb内部结构的优化。对于模糊匹配，全文搜索或外部搜索引擎可能是更优的选择。

3. -> vs ->>：
   • ->：返回JSON对象或数组的指定键/索引处的JSON值。结果仍是jsonb类型。
   • ->>：返回JSON对象或数组的指定键/索引处的JSON值，但结果是文本字符串。 在我们的场景中，由于需要进行字符串匹配，->>是正确的选择。
4. MySQL JSON_EXTRACT 的区别： 原始问题中提到的JSON_EXTRACT是MySQL的函数，其语法和功能与PostgreSQL的JSON函数不同。在PostgreSQL中，我们使用上述的->、->>操作符以及jsonb_array_elements()等函数来实现类似的功能。

总结

通过本教程，我们学习了如何在PostgreSQL中利用其强大的JSON/JSONB功能，精确地查询JSON数组内特定键的值。通过jsonb_array_elements()展开数组，结合->>操作符提取文本值，并使用EXISTS子查询进行过滤，我们可以构建出高效且准确的SQL查询，避免了将整个JSON结构转换为文本进行模糊匹配的潜在问题。遵循这些最佳实践，将有助于你更好地管理和查询PostgreSQL中的JSON数据。

以上就是在PostgreSQL中查询JSON数组内指定键值对的技巧的详细内容，更多请关注php中文网其它相关文章！