本頁說明使用 Spanner 時如何處理 JSONB 資料型別。
JSONB 是 PostgreSQL 資料型別，用於在 Spanner PostgreSQL 方言中保存半結構化資料。JSONB 包含 JavaScript Object Notation (JSON) 格式的資料，遵循 RFC 7159 中說明的規格。
規格
Spanner JSONB 資料類型會儲存輸入文件的正規化表示法。這表示：
- 不會保留引號和空白字元。
- 不支援留言。含註解的交易或查詢會失敗。
- 物件鍵會先依鍵長度排序，再依同等物件鍵長度排序。如有重複的物件鍵，系統只會保留最後一個。
- 原始型別 (
string、boolean、number和null) 會保留型別和值。string類型值會完全保留。- 系統會保留尾隨的零。
number類型值的輸出格式不會使用科學記數法。
JSONBnull值會視為 SQL 非NULL。舉例來說：SELECT null::jsonb IS NULL; -- Returns true SELECT 'null'::jsonb IS NULL; -- Returns false SELECT '{"a":null}'::jsonb -> 'a' IS NULL; -- Returns false SELECT '{"a":null}'::jsonb -> 'b' IS NULL; -- Returns true SELECT '{"a":null}'::jsonb -> 'a'; -- Returns a JSONB 'null' SELECT '{"a":null}'::jsonb -> 'b'; -- Returns a SQL NULL系統會保留 JSONB 陣列元素的順序。
限制
使用 Spanner JSONB 時，有下列限制：
to_jsonb函式的引數只能是 Spanner 支援的 PostgreSQL 資料類型。- 數字類型的值小數點前最多可有 4,932 位數，小數點後最多可有 16,383 位數。
- 標準化儲存格式的大小上限為 10 MB。
JSONB文件必須採用 UTF-8 編碼。如果交易或查詢的JSONB文件是以其他格式編碼，系統會傳回錯誤。
建立含有 JSONB 資料欄的資料表
建立資料表時，可以新增 JSONB 資料欄。
CREATE TABLE Venues (
VenueId BIGINT PRIMARY KEY,
VenueName VARCHAR(1024),
VenueAddress VARCHAR(1024),
VenueFeatures JSONB,
DateOpened TIMESTAMPTZ
);
以下是 VenueFeatures JSONB 物件的範例：
{
"rating": 4.5,
"capacity":"1500",
"construction":"brick",
"tags": [
"multi-cuisine",
"open-seating",
"stage",
"public address system"
]
}
在現有資料表中新增及移除 JSONB 資料欄
您可以新增 JSONB 資料欄，並使用 ALTER 陳述式捨棄該資料欄，如下所示：
ALTER TABLE Venues ADD COLUMN VenueDetails JSONB;
ALTER TABLE Venues DROP COLUMN VenueDetails;
以下範例說明如何使用 Spanner 用戶端程式庫，在 Venues 資料表中新增名為 VenueDetails 的 JSONB 資料欄。
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
修改 JSONB 資料
您可以像修改其他資料欄一樣修改 JSONB 資料欄。
範例如下：
UPDATE Venues SET VenueFeatures = '{"rating": 4.5, "tags":["multi-cuisine", "open-seating"] }'
WHERE VenueId = 1;
以下範例顯示如何使用 Spanner 用戶端程式庫更新 JSONB 資料。
C++
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
C#
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
Go
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
Java
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
Node.js
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
PHP
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
Python
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
Ruby
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
為 JSON 資料建立索引
您可以搭配使用次要索引和 搜尋索引與 JSONB 資料，加快查詢 JSONB 資料的速度。Spanner 不支援將 JSONB 類型資料欄做為次要索引中的鍵。
使用次要索引
在 JSONB 文件中針對純量值進行篩選時，次要索引非常實用。如要搭配 JSONB 使用次要索引，請建立產生的資料欄，擷取相關純量資料並轉換為適當的 SQL 資料類型。然後，您可以在這個產生的資料欄上建立次要索引。索引可加快對所產生資料欄執行的合格查詢。
在下列範例中，您會建立 VenuesByCapacity 索引，資料庫會使用該索引尋找容量大於 1000 的場地。Spanner 會使用索引找出相關資料列，而非檢查每個資料列，因此可提升查詢效能，尤其是大型資料表。
ALTER TABLE Venues (
ADD COLUMN VenueCapacity BIGINT GENERATED ALWAYS AS ((VenueFeatures->>'capacity')::BIGINT) VIRTUAL,
DateOpened TIMESTAMPTZ
);
CREATE INDEX VenuesByCapacity ON Venues(VenueCapacity);
SELECT VenueName
FROM Venues
WHERE VenueCapacity > 1000;
使用搜尋索引
查詢動態或多樣的 JSONB 文件時，搜尋索引就很有用。與次要索引不同，您可以針對儲存在 JSONB 欄位中的任何 JSONB 文件建立搜尋索引。搜尋索引會自動適應 JSON 文件、不同資料列之間和一段時間內的變化。
在下列範例中，您會建立 VenuesByVenueDetails 搜尋索引，資料庫會使用該索引尋找具有特定詳細資料 (例如大小和營業時間) 的場地。Spanner 不會檢查每個資料列，而是使用索引找出相關資料列，藉此提升查詢效能，尤其是大型資料表。
ALTER TABLE Venues
ADD COLUMN VenueDetails_Tokens spanner.tokenlist
GENERATED ALWAYS AS (spanner.tokenize_jsonb(VenueDetails)) VIRTUAL HIDDEN;
CREATE SEARCH INDEX VenuesByVenueDetails
ON Venues (VenueDetails_Tokens);
SELECT VenueName
FROM Venues
WHERE VenueDetails @> '{"labels": ["large"], "open": {"Friday": true}}'::jsonb;
詳情請參閱「JSON 搜尋索引」。
查詢 JSONB 資料
您可以根據基礎欄位的值查詢 JSONB 欄。以下範例會從 Venues 擷取 VenueId 和 VenueName，其中 VenueFeatures 的 rating 值大於 3.5。
SELECT VenueId, VenueName
FROM Venues
WHERE (VenueFeatures->>'rating')::FLOAT8 > 3.5;
以下範例顯示如何使用 Spanner 用戶端程式庫查詢 JSONB 資料。
C++
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
C#
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
Go
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
Java
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
Node.js
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
PHP
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
Python
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
Ruby
如要瞭解如何安裝及使用 Spanner 的用戶端程式庫，請參閱這篇文章。
如要向 Spanner 進行驗證，請設定應用程式預設憑證。 詳情請參閱「為本機開發環境設定驗證」。
不支援的 PostgreSQL JSONB 功能
Spanner JSONB 不支援下列開放原始碼 PostgreSQL JSONB 功能：
- 排序、比較和匯總
- PrimaryKey 和 ForeignKey
- 索引，包括 GIN 索引。您可以改用 Spanner 搜尋索引，加快與 GIN 索引相同的 JSONB 作業。詳情請參閱為 JSON 資料建立索引。
- 將
JSONB資料欄變更為任何其他資料類型，或從任何其他資料類型變更為JSONB - 在採用 PostgreSQL 連線協定的工具中，使用含未輸入型別 JSONB 參數的參數化查詢
在查詢引擎中使用強制轉換。與開放原始碼 PostgreSQL 不同，系統不支援從
JSONB隱含強制轉換為文字。您必須使用從JSONB型別進行的明確轉換，才能比對函式簽章。例如：SELECT concat('abc'::text, '{"key1":1}'::jsonb); -- Returns error SELECT concat('abc'::text, CAST('{"key1":1}'::jsonb AS TEXT)); -- This works SELECT 3 + CAST('5'::jsonb AS INTEGER); -- This works