查询半结构化数据

使用 Amazon Redshift，可以在查询和分析结构化数据的同时查询和分析半结构化数据，如 JSON、Avro 或 Ion。半结构化数据是指具有灵活架构的数据，支持分层结构或嵌套结构。以下各节将演示如何使用 Amazon Redshift 对开放数据格式的支持来查询半结构化数据，使您能够从复杂的数据结构中获取有价值的信息。

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

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

Amazon Redshift 使用 PartiQL 分别通过 [...] 括号和点符号来支持对数组和结构的导航。此外，您还可以使用点记法将导航混合到结构中，使用括号符号将数组混合到结构中。例如，以下语句仅选择嵌套在 SUPER 对象一层深的数组中的第三个元素：


SELECT super_object.array[2];
         
 array
---------------
 third_element

在执行数据操作（例如筛选、联接和聚合）时，可以使用点和括号符号。可以在通常存在列引用的查询中的任何位置使用这些符号。例如，以下语句选择类型为 UPDATED 的事件数。


SELECT COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0]."eventType" = 'UPDATED';
         
 eventType | count
-----------+-------
 "UPDATED" | 1

有关使用 PartiQL 导航的更深入示例，请参阅在 Amazon Redshift 中使用半结构化数据的示例。

取消嵌套查询

为了取消嵌套查询，Amazon Redshift 提供了两种遍历 SUPER 数组的方法：PartiQL 语法和 FROM 子句中的 UNNEST 操作。两种取消嵌套的方法会得到相同的输出。有关 UNNEST 操作的信息，请参阅 FROM 子句。有关使用 UNNEST 操作的示例，请参阅 UNNEST 示例。

Amazon Redshift 可以在查询的 FROM 子句中使用 PartiQL 语法来导航 SUPER 数组。使用前面的示例，以下示例对 c_orders 的属性值进行迭代。


SELECT orders.*, o FROM customer_orders orders, orders.c_orders o;

使用 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 element, index 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;

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

有关 FROM 子句的更多信息，请参阅FROM 子句。有关取消嵌套 SUPER 查询的更多示例，请参阅在 Amazon Redshift 中使用半结构化数据的示例。

对象逆透视

为执行逆透视，Amazon Redshift 使用 PartiQL 语法迭代 SUPER 对象。它使用查询的 FROM 子句以及 UNPIVOT 关键字来执行此操作。在以下示例中，表达式是 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 来迭代所有属性。请考虑以下语法：


UNPIVOT expression 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 子句。有关将透视与 SUPER 类型结合使用的示例，请参阅在 Amazon Redshift 中使用半结构化数据的示例。

动态键入

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

考虑以下示例，此示例查找订单的状态为 shipped 的行：


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

当 c_orders[0].o_orderstatus 的值为字符串“shipped”时，此示例查询中的等号计算为 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;

Order by（排序依据）

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

有关在 SUPER 查询中使用 GROUP BY 和 ORDER BY 的示例，请参阅筛选半结构化数据。

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

卸载半结构化数据

运算符和函数