在 JSON 數組中查找屬性組合的索引

基於關於 SO 的相關問題。

如果您只想要簡單的匹配，這很好用。假設JSON 數據中tracks同時具有Artist和Title欄位。所以我們有類似的東西：

INSERT INTO tracks (id, data)
VALUES (1, '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]');

我們像在原始問題中一樣創建索引：

CREATE INDEX tracks_artists_gin_idx ON tracks
USING GIN (json2arr(data, 'artist'));

CREATE INDEX tracks_title_gin_idx ON tracks
USING GIN (json2arr(data, 'title'));

所以現在我們有兩個欄位要匹配。如您所見，如果我們執行以下原始查詢（進行了非常幼稚的修改）：

SELECT *
FROM   tracks
WHERE  '{"ARTIST NAME"}'::text[] <@ (json2arr(data, 'artist'))
AND    '{"TITLE"}'::text[]       <@ (json2arr(data, 'title'))

這將給出錯誤的答案，因為 JSON 數組中的藝術家和標題數組的索引不必匹配此查詢以匹配 JSON 中的某些內容。執行此查詢的正確方法是什麼，以便我們可以獲得所需的精確匹配？json2arr()需要改嗎？

編輯：為什麼這是錯誤的

假設我們的表有如下記錄：

INSERT INTO tracks (id, data)  VALUES
 (1, '[{"artist": "Simple Plan",    "title": "Welcome to My Life"}]')
, (2, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
       {"artist": "Simple Plan",    "title": "Perfect"}]');

如果您查詢如下：

SELECT *
FROM   tracks
WHERE  '{"Simple Plan"}'::text[]        <@ (json2arr(data, 'artist'))
AND    '{"Welcome to my Life"}'::text[] <@ (json2arr(data, 'title'))

兩條記錄都將匹配（記錄 1 和 2），即使您真的只想要第一條記錄。

為什麼？

artist並且必須在 JSON 數組的同一title元素中匹配的明確要求不會反映在您的查詢中，它會查找至少一個元素與 匹配且另一個（可能是不同的）與.artist``title

您的第一個案例的範例數據不確定，因為對於單個數組元素，查詢不會以這種方式失敗。不過，您的第二個範例很好地說明了這種情況。

Postgres 9.3 中的解決方案

有多種方法可以解決此問題。一種方法是將每個 json 數組元素轉換為複合類型的 SQL 數組，該數組由artist和title匹配整個類型組成。

另一種方法是保留您現在擁有的索引（甚至是跨越兩個表達式的單個複合：(json2arr(data, 'artist'), json2arr(data, 'title'))。您目前的查詢辨識可能的data匹配項。取消所有已辨識候選人的 json 數組artist並檢查兩者是否title匹配相同的元素。可能或可能效率不夠。

jsonb在 n Postgres 9.4 中更好地使用

我不會詳細介紹json/pg 9.3，因為**jsonbPostgres 9.4中具有高級索引功能的發布大部分已經過時了這個問題**。使用列上的本機 GIN 索引可以更簡單、更高效地實現這一點jsonb。有多種選擇。為了優化呈現的案例：

CREATE TABLE tracks (id serial, data **jsonb**);

INSERT INTO tracks (id, data)  VALUES
 (1, '[{"artist": "Simple Plan",    "title": "Welcome to My Life"}]')
, (2, '[{"artist": "Another Artist", "title": "Welcome to My Life"},
       {"artist": "Simple Plan",    "title": "Perfect"}]');

指數：

CREATE INDEX tracks_data_gin_idx ON tracks USING gin (data jsonb_path_ops);

詢問：

SELECT * FROM tracks
WHERE  data @> '[{"artist": "Simple Plan", "title": "Welcome to My Life"}]';

JSON 值中的屬性序列和無關緊要的空格對於jsonb. 我在 SO 上的參考答案中添加了 jsonb 的詳細資訊：

• 在 JSON 數組中查找元素的索引

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