序列化複雜的巢狀 JSON

本教學課程中示範方法的替代方法是以序列化 JSON 形式查詢頂層巢狀集合欄。您可以透過 Redshift Spectrum 使用序列化來檢查、轉換巢狀資料並將其提取為 JSON。ORC、JSON、Ion 和 Parquet 格式支援此方法。使用工作階段組態參數 json_serialization_enable 來設定序列化行為。設定時，複雜的 JSON 資料類型會序列化為 VARCHAR(65535)。您可以使用 JSON 函數 存取巢狀 JSON。如需詳細資訊，請參閱json_serialization_enable。

例如，如果沒有設定 json_serialization_enable，下列直接存取巢狀欄的查詢會失敗。

SELECT * FROM spectrum.customers LIMIT 1;

=> ERROR:  Nested tables do not support '*' in the SELECT clause.

SELECT name FROM spectrum.customers LIMIT 1;

=> ERROR:  column "name" does not exist in customers

設定 json_serialization_enable 可讓您直接查詢頂層集合。

SET json_serialization_enable TO true;

SELECT * FROM spectrum.customers order by id LIMIT 1;

id | name                                 | phones         | orders
---+--------------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------
1  | {"given": "John", "family": "Smith"} | ["123-457789"] | [{"shipdate": "2018-03-01T11:59:59.000Z", "price": 100.50}, {"shipdate": "2018-03-01T09:10:00.000Z", "price": 99.12}]          
 
SELECT name FROM spectrum.customers order by id LIMIT 1;

name
---------
{"given": "John", "family": "Smith"}  

序列化巢狀 JSON 時，請考慮下列項目。

• 當集合欄序列化為 VARCHAR(65535) 時，無法直接存取其巢狀子欄位做為查詢語法的一部分 (例如，在篩選條件子句中)。但是，JSON 函數可用於存取巢狀 JSON。

• 不支援下列特殊表示法：

  • ORC 聯集

  • 具有複雜類型鍵的 ORC 映射

  • Ion 資料包

  • Ion SEXP

• 時間戳記會傳回為 ISO 序列化字串。

• 原始映射鍵被提升為字串 (例如，1 到 "1")。

• 頂層 null 值會序列化為 NULL。

• 如果序列化超出最大 VARCHAR 的大小 65535，則儲存格將設為 NULL。

序列化包含 JSON 字串的複雜類型

預設情況下，巢狀集合中包含的字串值將序列化為逸出的 JSON 字串。當字串是有效的 JSON 時，逸出可能是不可取的。相反，您可能希望將 VARCHAR 的巢狀子元素或欄位直接編寫為 JSON。使用 json_serialization_parse_nested_strings 工作階段層級組態啟用此行為。同時設定 json_serialization_enable 和 json_serialization_parse_nested_strings 時，有效的 JSON 值會以內嵌方式序列化，不含逸出字元。當值不是有效的 JSON 時，會將其逸出，就好像未設定 json_serialization_parse_nested_strings 組態值一樣。如需詳細資訊，請參閱json_serialization_parse_nested_strings。

例如，假設上一個範例中的資料在 name VARCHAR(20) 欄位中包含 JSON 作為 structs 複雜類型：

name
---------
{"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"}

設定 json_serialization_parse_nested_strings 時，name 欄會依照下列方式序列化：

SET json_serialization_enable TO true;
SET json_serialization_parse_nested_strings TO true;
SELECT name FROM spectrum.customers order by id LIMIT 1;

name
---------
{"given": {"first":"John","middle":"James"}, "family": "Smith"}

而不是這樣逸出：

SET json_serialization_enable TO true;
SELECT name FROM spectrum.customers order by id LIMIT 1;

name
---------
{"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"}