MySQL中从多维数组JSONB筛选特定结果的方法

你的数据库中存储的JSONB数据如下：

#1   [["status", "discontinued", ""], ["profile", "strong", ""]]
#2   [["profile", "mildly", ""], ["status", "discontinued", ""]]
#3   [["status", "discontinued"]]

需求是筛选出所有包含profile键且对应值为strong的条目，你之前执行的select article_group_extra->>'$[*]' from products;仅将整个JSON数组转为文本输出，没有实现筛选逻辑。

可以用以下两种方法实现需求：

方法一：展开数组后筛选 #

通过jsonb_array_elements将JSONB数组展开为多行，再检查是否存在符合条件的子数组：

SELECT *
FROM products
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(article_group_extra) AS elem
    WHERE elem ->> 0 = 'profile' AND elem ->> 1 = 'strong'
);

方法二：使用JSON路径表达式 #

利用PostgreSQL的JSON路径功能直接匹配符合条件的子数组：

SELECT *
FROM products
WHERE jsonb_path_exists(article_group_extra, '$[*] ? (@[0] == "profile" && @[1] == "strong")');

这两种方法都能忽略数组中子元素的顺序，且能处理profile字段不存在的情况，最终只会返回#1这条符合条件的记录。

内容的提问来源于stack exchange，提问作者Digital Forge