關於嵌套 JSON 值的 OUTER JOIN 警告

我正在編寫一個查詢，該查詢應該從數據庫中不存在的列表中查找元素。我對此的第一次嘗試是使用嵌套查詢，其中第一個查詢獲取 id，然後我right join在該查詢上獲取我需要的內容，這很好用：

select v.id from (
   select distinct json_data ->> 'elementId' as elementId
   from content
   and json_data->> 'elementId' in ('id1', 'id2', 'id3')
) as a
right join (values('id1'), ('id2'), ('id3')) as v(id)
on v.id = a.elementId
where a.elementId is null

select上面的查詢非常完美，除了我想要的事實，如果我直接進行比較，我應該能夠將嵌套查詢減少為正常查詢json_data ->> 'elementId'。

我的嘗試：

select v.id
from content a
right join (values('id1'), ('id2'), ('id3')) as v(id)
on json_data ->> 'elementId' = v.id

經過一些調試後，我意識到這永遠不會起作用，因為content即使json_data ->>'elementId'是，該表也將始終包含一行null。

**編輯：**我有一個額外的WHERE聲明，在問題中沒有說明，一旦我在ON我的查詢被修復後移動了這個

我的問題是；當想要對數據執行left join或操作時，有沒有辦法避免使用嵌套查詢？right join JSON

使用NOT EXISTS：

SELECT *
FROM  (VALUES ('id1'), ('id2'), ('id3')) AS v(id)
WHERE  NOT EXISTS (SELECT FROM content WHERE json_data ->> 'elementId' = v.id);

或者，如果您更喜歡join：

SELECT v.id
FROM  (VALUES ('id1'), ('id2'), ('id3')) AS v(id)
LEFT   JOIN content c ON c.json_data ->> 'elementId' = v.id
WHERE  c.json_data IS NULL -- or use the PK column

從技術上講，要麼是“反加入”；並且兩者都可能導致相同的查詢計劃。

看：

• 選擇其他表中不存在的行

考慮升級到目前版本的 Postgres。9.4 已於 2020 年 2 月達到 EOL。

指數

但即使是 Postgres 9.4 也已經支持jsonbwhich (不像json) 允許 GIN 索引來支持您的查詢。看：

• 如何從 PostgreSQL 中的 jsonb 數組中獲取特定對象？
• 在 Postgres jsonb 中查詢數組結構的正確索引是什麼？

或者，如果您只關注此查詢，則表達式上的普通 btree應該是最佳選擇：

CREATE INDEX ON content ((json_data ->> 'elementId'));

有關的：

• PostgreSQL 運算符使用索引但底層函式不使用

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