Zenn
🍉

PostgreSQL の jsonb 型の配列に対して some/every する

2022/08/05に公開
PostgreSQL
JSON
tech

JavaScript でいうところの some/every(他の言語では any/all とか)を、PostgreSQL の JSONB 型の列値に対して行う。

const row = ['すいか', 'キャベツ', 'いちご'];
const query = ['すいか', 'はくさい'];

console.log(
  // query のいずれかを含む → true
  query.some(q => row.find(x => x === q) != null),  
  // query の全てを含む → false
  query.every(q => row.find(x => x === q) != null), 
);

農作物 テーブル

畑名 作物群
畑1 ["はくさい", "いちご", "すいか"]
畑2 ["はくさい" ]
畑3 ["いちご", "キャベツ" ]
畑4 ["はくさい", "すいか" ]
畑5 ["すいか" ]
畑6 { items: ["キャベツ", "いちご" ] }

はくさい と すいか をどちらも作っている畑を抽出

SELECT 畑名 FROM 農作物 WHERE 作物群 ?& array['はくさい', 'すいか'];

→ 畑1, 畑4

はくさい または すいか を作っている畑を抽出

SELECT 畑名 FROM 農作物 WHERE 作物群 ?| array['はくさい', 'すいか'];

→ 畑1, 畑2, 畑4, 畑5

JSON の子要素に対して検索する場合

※列に格納する JSON の仕様は統一しましょう。
※JSONB型列のルートを [ ] にしちゃうのは、拡張性を殺しているのでバッドパターンと思います。

SELECT 畑名 FROM 農作物 WHERE 作物群->"items" ?| array['キャベツ'];

→ 畑6

参考

Discussion