如何在 PostgreSQL 中查詢巢狀 JSON 陣列？

PostgreSQL中巢狀JSON陣列的查詢

簡介：

PostgreSQL的JSON資料類型提供了全面的資料儲存和查詢功能。一個常見的挑戰是查詢儲存在JSON物件中的陣列。本文提供了一個逐步指導，用於查詢JSON類型中的數組元素，重點關注PostgreSQL 9.3及以上版本。

在PostgreSQL 9.3以上版本查詢JSON陣列：

1. json_array_elements() 函數：

   json_array_elements() 函數傳回JSON陣列中所有元素的橫向列表。它可以與FROM子句中的橫向連接一起使用：

   <code class="language-sql">SELECT data::text, obj
   FROM   reports r, json_array_elements(r.data#>'{objects}') obj
   WHERE  obj->>'src' = 'foo.png';</code>

   請注意，#> 運算子用於存取JSON陣列中的巢狀物件。

2. 巢狀橫向連結：

   另一種方法是使用巢狀橫向連接：

   <code class="language-sql">SELECT *
   FROM   reports r, json_array_elements(r.data->'objects') obj
   WHERE  obj->>'src' = 'foo.png';</code>

   此查詢使用隱式JOIN LATERAL，其操作方式與前面的範例類似。

使用GIN索引最佳化查詢（PostgreSQL 9.4 ）：

為了提高搜尋特定數組元素時的查詢效能，建議使用jsonb_array_elements()並在JSON表達式data->'objects'上建立GIN索引：

<code class="language-sql">CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';</code>

@> 運算子檢查包含關係，並要求JSON結構用方括號括起來。

附加說明：

• ->, ->> 和 #> 運算子用於存取巢狀的JSON物件和陣列。
• 有關JSON運算子的全面了解，請參閱PostgreSQL文件。

以上是如何在 PostgreSQL 中查詢巢狀 JSON 陣列？的詳細內容。更多資訊請關注PHP中文網其他相關文章！