PostgreSQL：在數組中查找具有 JSON 對象屬性的行

我有一個 postgres 數據庫，它是這樣設置的：

CREATE TABLE arr_test (
 id serial primary key,
 data jsonb
);

INSERT INTO arr_test (data) VALUES 
 ('{"test_row": 1, "my_arr": [{"serial":"AAA", "content":"123"}, {"serial":"BBB", "content":"345"}]}'),
 ('{"test_row": 2, "my_arr": [{"serial":"CCC", "content":"456"}, {"serial":"DDD", "content":"567"}]}'),
 ('{"test_row": 3, "my_arr": [{"serial":"AAA", "content":"678"}, {"serial":"EEE", "content":"789"}]}');

我正在嘗試編寫一個查詢，其中數據包含一個列表，該列表具有一個包含值的對象。在這種情況下，它是我要查詢的數組中對象的序列欄位。

例如，如果我查詢串列“AAA”，我希望返回測試行 1 和 3，如果我查詢串列“EEE”，我只想測試第 3 行。

我已經嘗試過jsonb_array_elements，jsonb_to_recordset但一無所獲，希望能提供任何幫助。

您需要取消嵌套數組元素並將其與 EXISTS 條件組合

select t.*
from arr_test t
where exists (select *
             from jsonb_array_elements(t.data -> 'my_arr') as x(o)
             where x.o ->> 'serial' = 'AAA');

或者，您可以使用 contains 運算符編寫@>：

select t.*
from arr_test t
where exists (select *
             from jsonb_array_elements(t.data -> 'my_arr') as x(o)
             where x.o @> '{"serial": "AAA"}');

在 dbfiddle 上試試

這也可以工作

select
t.id,
jsonb_path_query(data, '$ ? (@.my_arr[*].serial == "AAA")')
from arr_test t

來自官方文件： JSON 函式和運算符

jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

返回指定 JSON 值的 JSON 路徑返回的所有 JSON 項。可選的 vars 和靜默參數的作用與 jsonb_path_exists 相同。

select * from jsonb_path_query('{"a":[1,2,3,4,5]}', $.a[*] ? (@ >= $min && @ <= $max)', 

'{"min":2, "max":4}') →

jsonb_path_query
------------------
2
3
4

關於與上一個答案的性能比較，我不知道。jsonb 函式中的索引意識並不是最重要的，但隨著每個 Postgres 版本的發布，它會變得更好。

引用自：https://dba.stackexchange.com/questions/242625