Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

查询半结构化数据

Amazon Redshift 使用 PartiQL 语言提供对关系数据、半结构化数据和嵌套数据的 SQL 兼容访问。

PartiQL 使用动态类型进行操作。这种方法可以对结构化、半结构化和嵌套数据集的组合进行直观的筛选、联接和聚合。在访问嵌套数据时，PartiQL 语法使用点记法和数组下标进行路径导航。它还使 FROM 子句项能够对数组进行迭代并用于非嵌套操作。以下内容介绍了将 SUPER 数据类型的使用与路径和数组导航、取消嵌套、逆透视转换和联接相结合的不同查询模式。

有关以下示例中使用的表的信息，请参阅SUPER sample 数据集。

导航

Amazon Redshift 使用 PartiQL 分别通过 [...] 括号和点符号来支持对数组和结构的导航。此外，您还可以使用点记法将导航混合到结构中，使用括号符号将数组混合到结构中。例如，以下示例假定 c_orders SUPER 数据列是一个具有结构的数组，并且属性名为 o_orderkey。

要提取 customer_orders_lineitem 表中的数据，请运行以下命令。将 IAM 角色替换为您自己的凭证。

COPY customer_orders_lineitem FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/customer_orders_lineitem'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'auto';

SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem;

Amazon Redshift 还使用表别名作为表示法的前缀。以下示例是与上一个示例相同的查询。

SELECT cust.c_orders[0].o_orderkey FROM customer_orders_lineitem AS cust;

您可以在所有类型的查询中使用点和括号符号，例如筛选、联接和聚合。您可以在通常存在列引用的查询中使用这些符号。以下示例使用筛选结果的 SELECT 语句。

SELECT count(*) FROM customer_orders_lineitem WHERE c_orders[0]. o_orderkey IS NOT NULL;

以下示例在 GROUP BY 和 ORDER BY 子句中使用括号和点导航：

SELECT c_orders[0].o_orderdate,
       c_orders[0].o_orderstatus,
       count(*)
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderkey IS NOT NULL
GROUP BY c_orders[0].o_orderstatus,
         c_orders[0].o_orderdate
ORDER BY c_orders[0].o_orderdate;

取消嵌套查询

为了取消嵌套查询，Amazon Redshift 使用 PartiQL 语法迭代 SUPER 数组。它通过使用查询的 FROM 子句导航数组来实现这一点。使用前面的示例，以下示例对 c_orders 的属性值进行迭代。

SELECT c.*, o FROM customer_orders_lineitem c, c.c_orders o;

取消嵌套语法是 FROM 子句的扩展。在标准 SQL 中，FROM 子句 x (AS) y 表示 y 迭代关系 x 中的每个元组。在这种情况下，x 指的是关系，而 y 指的是关系 x 的别名。同样，使用 FROM 子句项 x (AS) y 进行取消嵌套的 PartiQL 语法表示 y 迭代（SUPER）数组表达式 x 中的每个（SUPER）值。在这种情况下，x 是一个 SUPER 表达式，而 y 是 x 的别名。

左侧操作数也可以使用点和括号表示法进行常规导航。在上一个示例中，customer_orders_lineitem c 是对 customer_order_lineitem 基表的迭代，c.c_orders o 是对 c.c_orders 数组的迭代。要迭代作为数组中的数组的 o_lineitems 属性，必须添加多个子句。

SELECT c.*, o, l FROM customer_orders_lineitem c, c.c_orders o, o.o_lineitems l;

Amazon Redshift 还在使用 AT 关键字迭代数组时支持数组索引。子句 x AS y AT z 迭代数组 x 并生成字段 z,，即数组索引。以下示例演示数组索引的工作原理：

SELECT c_name,
       orders.o_orderkey AS orderkey,
       index AS orderkey_index
FROM customer_orders_lineitem c, c.c_orders AS orders AT index 
ORDER BY orderkey_index;

c_name             | orderkey | orderkey_index
-------------------+----------+----------------
Customer#000008251 | 3020007  |        0
Customer#000009452 | 4043971  |        0
  (2 rows)

以下示例对标量数组进行迭代：

CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data;

SELECT index, element FROM bar AS b, b.data.scalar_array AS element AT index;

 index | element
-------+----------
     0 | 1
     1 | 2.3
     2 | 45000000
(3 rows)

以下示例对多个级别的数组进行迭代。该示例使用多个 unnest 子句来迭代到最内层的数组。f.multi_level_array AS 数组迭代 multi_level_array。数组 AS 元素是对 multi_level_array 中的数组的迭代。

CREATE TABLE foo AS SELECT json_parse('[[1.1, 1.2], [2.1, 2.2], [3.1, 3.2]]') AS multi_level_array;

SELECT array, element FROM foo AS f, f.multi_level_array AS array, array AS element;

   array   | element
-----------+---------
 [1.1,1.2] | 1.1
 [1.1,1.2] | 1.2
 [2.1,2.2] | 2.1
 [2.1,2.2] | 2.2
 [3.1,3.2] | 3.1
 [3.1,3.2] | 3.2
(6 rows)

有关 FROM 子句的更多信息，请参阅FROM 子句。

对象逆透视

为执行逆透视，Amazon Redshift 使用 PartiQL 语法迭代 SUPER 对象。它使用带有 UNPIVOT 关键字的查询的 FROM 子句来执行此操作。以下查询迭代 c.c_orders[0] 对象。

SELECT attr as attribute_name, json_typeof(val) as value_type 
FROM customer_orders_lineitem c, UNPIVOT c.c_orders[0] AS val AT attr 
WHERE c_custkey = 9451;

 attribute_name  | value_type
-----------------+------------
 o_orderstatus   | string
 o_clerk         | string
 o_lineitems     | array
 o_orderdate     | string
 o_shippriority  | number
 o_totalprice    | number
 o_orderkey      | number
 o_comment       | string
 o_orderpriority | string
(9 rows)

与取消嵌套一样，逆透视语法也是 FROM 子句的扩展。不同之处在于，逆透视的语法使用 UNPIVOT 关键字来表示它正在迭代对象而不是数组。它使用 AS value_alias 迭代对象内的所有值并使用 AT attribute_alias 迭代所有属性。

Amazon Redshift 还支持在单个 FROM 子句中使用对象逆透视和数组取消嵌套，如下所示。

SELECT attr as attribute_name, val as object_value
FROM customer_orders_lineitem c, c.c_orders AS o, UNPIVOT o AS val AT attr 
WHERE c_custkey = 9451;

当您使用对象逆透视时，Amazon Redshift 不支持关联的逆透视。具体来说，假设您有一个案例，其中在不同查询级别有多个逆透视示例，并且内部逆透视引用了外部逆透视。Amazon Redshift 不支持此类多重逆透视。

有关 FROM 子句的更多信息，请参阅FROM 子句。有关演示如何使用 PIVOT 和 UNPIVOT 查询结构化数据的示例，请参阅 PIVOT 和 UNPIVOT 示例。

动态键入

动态键入不需要显式转换从点和括号路径中提取的数据。Amazon Redshift 使用动态键入处理无 schemal SUPER 数据，无需在查询中使用数据类型之前声明数据类型。动态键入使用导航到 SUPER 数据列的结果，而无需将其显式转换为 Amazon Redshift 类型。动态键入在联接和 GROUP BY 子句中最有用。以下示例使用 SELECT 语句，该语句不需要将点和括号表达式显式转换为常见的 Amazon Redshift 类型。有关类型兼容性和转换的信息，请参阅类型兼容性和转换。

SELECT c_orders[0].o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus = 'P';

当 c_orders[0].o_orderstatus 为字符串“P”时，此查询中的等号计算为 true。在所有其他情况下，等号计算为 false，包括等式参数为不同类型的情况。

动态和静态键入

如果不使用动态键入，则无法确定 c_orders[0].o_orderstatus 是字符串、整数还是结构。您只能确定 c_orders[0].o_orderstatus 是 SUPER 数据类型，它可以是 Amazon Redshift 标量、数组或结构。c_orders[0].o_orderstatus 的静态类型是 SUPER 数据类型。传统上，类型在 SQL 中是隐式的静态类型。

Amazon Redshift 使用动态键入来处理无 schema 数据。当查询计算数据时，c_orders[0].o_orderstatus 是一种特定的类型。例如，在 customer_orders_lineitem 的第一条记录上评估 c_orders[0].o_orderstatus 可能会导致一个整数。对第二条记录进行评估可能会导致字符串。它们都是表达式的动态类型。

当将 SQL 运算符或函数与具有动态类型的点和括号表达式一起使用时，Amazon Redshift 生成的结果类似于将标准 SQL 运算符或的函数与相应的静态类型结合使用。在此示例中，当路径表达式的动态类型为字符串时，与字符串“P”进行比较是有意义的。只要 c_orders[0].o_orderstatus 的动态类型是除字符串外的任何其他数据类型，相等性都返回 false。当使用键入错误的参数时，其他函数返回 null。

以下示例使用静态键入编写上一个查询：

SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR = 'P'
           ELSE FALSE END;

请注意，相等谓词和比较谓词之间存在以下区别。在上一个示例中，如果用小于或等于谓词替换相等谓词，则语义生成 null 而不是 false。

SELECT c_orders[0]. o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus <= 'P';

在此示例中，如果 c_orders[0].o_orderstatus 是一个字符串，则如果它的字母顺序等于或小于“P”，Amazon Redshift 返回 true。如果 Amazon Redshift 按字母顺序大于“P”，则返回 false。但是，如果 c_orders[0].o_orderstatus 不是字符串，则 Amazon Redshift 会返回 null，因为 Amazon Redshift 无法比较不同类型的值，如以下查询所示：

SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR <= 'P'
           ELSE NULL END;

动态键入并不排除具有最低可比性的类型的比较。例如，您可以将 CHAR 和 VARCHAR Amazon Redshift 标量类型转换为 SUPER。它们与字符串类似，包括忽略类似于 Amazon Redshift CHAR 和 VARCHAR 类型的尾随空格字符。同样地，整数、小数和浮点值可与 SUPER 值进行比较。特别是对于小数列，每个值也可以具有不同的小数位数。Amazon Redshift 仍将它们视为动态类型。

Amazon Redshift 还支持对深度相等的对象和数组进行相等运算，例如深入评估对象或数组以及比较所有属性。小心使用深度相等计算，因为执行深度相等的过程可能很耗时。

对联接使用动态键入

对于联接，动态键入会自动匹配值与不同的动态类型，而无需执行长 CASE WHEN 分析以找出可能显示的数据类型。例如，假定您的组织随着时间的推移更改了它用于部分键的格式。

发出的初始整数部分键被字符串部分键（如“A55”）替换，然后再次替换为数组部分键，例如字符串和数字组合形成的 [‘X’, 10]。Amazon Redshift 不必对部分键执行冗长的案例分析，并且可以如以下示例所示使用联接。

SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE l.l_partkey = ps.ps_partkey
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

下面的示例显示了，如果不使用动态键入，同一个查询会多么复杂和低效：

SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE CASE WHEN IS_INTEGER(l.l_partkey) AND IS_INTEGER(ps.ps_partkey)
           THEN l.l_partkey::integer = ps.ps_partkey::integer
           WHEN IS_VARCHAR(l.l_partkey) AND IS_VARCHAR(ps.ps_partkey)
           THEN l.l_partkey::varchar = ps.ps_partkey::varchar
           WHEN IS_ARRAY(l.l_partkey) AND IS_ARRAY(ps.ps_partkey)
                AND IS_VARCHAR(l.l_partkey[0]) AND IS_VARCHAR(ps.ps_partkey[0])
                AND IS_INTEGER(l.l_partkey[1]) AND IS_INTEGER(ps.ps_partkey[1])
           THEN l.l_partkey[0]::varchar = ps.ps_partkey[0]::varchar
                AND l.l_partkey[1]::integer = ps.ps_partkey[1]::integer
           ELSE FALSE END
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

宽松语义

预设情况下，在导航无效时，SUPER 值的导航操作返回 null，而不是返回错误。如果 SUPER 值不是对象，或者如果 SUPER 值是一个对象，但不包含查询中使用的属性名称，则对象导航无效。例如，以下查询访问 SUPER 数据列 cdata 中的无效属性名称：

SELECT c.c_orders.something FROM customer_orders_lineitem c;

如果 SUPER 值不是数组或数组索引超出界限，则数组导航返回 null。以下查询返回 null，因为 c_orders[1][1] 超出了界限。

SELECT c.c_orders[1][1] FROM customer_orders_lineitem c;

在使用动态键入转换 SUPER 值时，宽松语义特别有用。如果转换无效，将 SUPER 值转换为错误的类型将返回 null 而不是错误。例如，以下查询返回 null，因为它不能将对象属性 o_orderstatus 的字符串值“Good”转换为 INTEGER。Amazon Redshift 针对 VARCHAR 到 INTEGER 的转换返回错误，但不针对 SUPER 转换返回错误。

SELECT c.c_orders.o_orderstatus::integer FROM customer_orders_lineitem c;

自检类型

SUPER 数据列支持返回有关 SUPER 值的动态类型和其他类型信息的检查函数。最常见的示例是返回具有布尔值、数字、字符串、对象、数组或 null 的 VARCHAR 的 JSON_TYPEOF 标量函数，具体取决于 SUPER 值的动态类型。Amazon Redshift 支持以下针对 SUPER 数据列的布尔函数：

如果输入值为 null，所有这些函数都返回 false。IS_SCALAR、IS_OBJECT 和 IS_ARRAY 是相互排斥的，涵盖除 null 之外的所有可能的值。

要推理与数据对应的类型，Amazon Redshift 使用 JSON_TYPEOF 函数，该函数返回 SUPER 值的类型（顶级），如以下示例所示：

SELECT JSON_TYPEOF(r_nations) FROM region_nations;
 json_typeof
 -------------
 array
(1 row)

SELECT JSON_TYPEOF(r_nations[0].n_nationkey) FROM region_nations;
 json_typeof
 -------------
 number

Amazon Redshift 将此字符串视为单个长字符串，类似于将此值插入 VARCHAR 列而不是 SUPER。由于该列是 SUPER，因此单个字符串仍然是一个有效的 SUPER 值，且差异体现在 JSON_TYPEOF 中：

SELECT IS_VARCHAR(r_nations[0].n_name) FROM region_nations;
 is_varchar
-------------
 true
(1 row)

SELECT r_nations[4].n_name FROM region_nations
WHERE CASE WHEN IS_INTEGER(r_nations[4].n_nationkey) 
           THEN r_nations[4].n_nationkey::INTEGER = 15 
           ELSE false END;

Order by（排序依据）

Amazon Redshift 不会定义具有不同动态类型的值之间的 SUPER 比较。作为字符串的 SUPER 值既不小于也不大于作为数字的 SUPER 值。要将 ORDER BY 子句与 SUPER 列一起使用，Amazon Redshift 定义了在 Amazon Redshift 使用 ORDER BY 子句对 SUPER 值进行排名时，需要观察的不同类型的总排序。动态类型之间的顺序是布尔值、数字、字符串、数组、对象。以下示例显示不同类型的顺序：

INSERT INTO region_nations VALUES 
(100,'name1','comment1','AWS'), 
(200,'name2','comment2',1),
(300,'name3','comment3',ARRAY(1, 'abc', null)),
(400,'name4','comment4',-2.5),
(500,'name5','comment5','Amazon');

SELECT r_nations FROM region_nations order by r_nations;

r_nations
----------------
 -2.5
 1
 "Amazon"
 "AWS"
 [1,"abc",null]
(5 rows)

有关 ORDER BY 子句的更多信息，请参阅ORDER BY 子句。