PostgreSQL JSON列查询：如何筛选含指定K-V对的行

你的原查询语句错误原因在于：直接对整个JSON数组使用->>'k'操作符，会试图从数组对象本身获取键k，但数组并没有这个键，因此永远无法匹配到目标数据。要筛选数组中存在k='name'的对象，需要检查数组内的每个元素。

以下是几种可行的修正方案：

方案1：数组展开+EXISTS子查询（兼容所有支持JSON的PostgreSQL版本） #

通过json_array_elements将JSON数组拆分为单个元素，再检查每个元素的k值，最后用EXISTS判断是否存在符合条件的元素：

SELECT *
FROM public.node n
WHERE EXISTS (
    SELECT 1
    FROM json_array_elements(n.tags) AS elem
    WHERE elem->>'k' = 'name'
);

方案2：JSON包含操作符（PostgreSQL 9.3+） #

利用@>操作符判断JSON数组是否包含指定的对象，语法更简洁：

SELECT *
FROM public.node
WHERE tags @> '[{"k": "name"}]'::json;

方案3：JSON路径查询（PostgreSQL 12+） #

通过JSON路径表达式直接检查数组中是否存在满足条件的元素：

SELECT *
FROM public.node
WHERE json_path_exists(tags, '$[*] ? (@.k == "name")');

内容的提问来源于stack exchange，提问作者Anton Golovenko