PostgreSQL：基于数组嵌套对象中动态字段存在性查询表

Hey, I've run into similar jsonb array nesting issues before, so let's break this down for you.

Your users column is a jsonb array where each element has an agents object with dynamic user keys—you want to find rows where a specific user (like user1) exists in any of those agents objects, right? Here are a couple of solid approaches to do this in PostgreSQL:

Method 1: Unnest the Array with jsonb_array_elements #

This approach expands the jsonb array into individual rows, then checks for the presence of your target user key in the agents object:

SELECT DISTINCT item_id
FROM items,
     jsonb_array_elements(users) AS arr_element
WHERE arr_element->'agents' ? 'user1';

How it works: #

• jsonb_array_elements(users) takes the users array and returns each element as a separate row.
• The ? operator checks if the agents object (accessed via ->) contains the key user1.
• DISTINCT ensures we don't get duplicate item_id entries if multiple array elements match the condition.

Method 2: Use jsonb_path_exists for a Concise Query #

If you prefer not to unnest the array, PostgreSQL's json path functions let you query directly within the nested structure:

SELECT item_id
FROM items
WHERE jsonb_path_exists(users, '$[*].agents ? (@.has_key("user1"))');

How it works: #

• $[*] iterates over every element in the users array.
• .agents accesses the agents object of each array element.
• @.has_key("user1") checks if the current agents object contains the key user1.

Bonus: Dynamic Parameter Support #

If you need to pass the user key as a variable (instead of hardcoding it), you can use a parameterized query with jsonb_path_exists:

-- Replace 'user1' with your parameter (works in psql, app frameworks like Django/Node.js, etc.)
SELECT item_id
FROM items
WHERE jsonb_path_exists(users, '$[*].agents ? (@.has_key($1))', to_jsonb('user1'::text));

Bonus: Check for Multiple Users #

If you want to find rows where any of several users exist in the agents objects, use the ?| operator with an array of keys:

SELECT DISTINCT item_id
FROM items,
     jsonb_array_elements(users) AS arr_element
WHERE jsonb_object_keys(arr_element->'agents') ?| ARRAY['user1', 'user4'];

Choosing the Right Method: #

• Use the unnest approach if you need to add additional filters (e.g., check the required value alongside the user key).
• Use jsonb_path_exists for cleaner, more concise queries when you only need to check key existence.

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