如何在SELECT语句中查询jsonb数组的指定键值匹配记录？

First, let's figure out why your initial query SELECT meta::json->0 FROM myTable returned null. Since your meta field is already jsonb, casting it to json isn't necessary—using meta->0 directly would work if meta is a valid non-empty array. If you're getting null, either:

• The meta field isn't actually a JSON array (you can verify this with SELECT jsonb_typeof(meta) FROM myTable), or
• The array itself is empty, so there's no element at index 0.

Now, to filter records where any element in the meta array has FieldName='wire1', Source='exampleSource', or both, here are two reliable approaches:

Approach 1: Using EXISTS with jsonb_array_elements #

This method is explicit and easy to tweak for more complex conditions:

SELECT * 
FROM myTable
WHERE 
  -- Ensure meta is a valid array to avoid errors
  jsonb_typeof(meta) = 'array'
  AND EXISTS (
    SELECT 1 
    FROM jsonb_array_elements(meta) AS elem
    WHERE 
      elem->>'FieldName' = 'wire1' 
      OR elem->>'Source' = 'exampleSource'
  );

Breakdown: #

• jsonb_array_elements(meta) splits the JSON array into individual rows of jsonb elements.
• elem->>'FieldName' pulls the FieldName value as plain text (use -> instead if you need the raw JSON value).
• The EXISTS clause checks if at least one element in the array meets your criteria for the row.

Approach 2: Using jsonb_path_exists (Concise JSONPath Syntax) #

If you prefer a more compact query, PostgreSQL supports JSONPath expressions for this kind of filtering:

SELECT * 
FROM myTable
WHERE jsonb_path_exists(
  meta, 
  '$[*] ? (@.FieldName == "wire1" || @.Source == "exampleSource")'
);

Breakdown: #

• $[*] targets every element in the array.
• ? (...) filters elements that match the condition inside.
• @ refers to the current element being evaluated.

Both approaches will return all rows where the meta array contains at least one element matching your requirements. If you need to retrieve the matching elements themselves (not just the parent rows), you can adjust the query to include jsonb_array_elements in the main SELECT clause.

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