OpenSearch:Child table queries

Overview

A child table of HA3 is registered as a compound field in IQUAN. You can use this field as a field of the struct type. The name of the child table is the name of the compound field. The fields of the child table cannot be directly queried. When you query the child table, run the UNNEST function to expand the table. This operation is semantically equivalent to the inner join operation that you can perform on a parent table and child tables.

Supported versions

OpenSearch Retrieval Engine Edition whose HA3 version is V3.7.0 or later

Syntax

select:
  SELECT [ ALL | DISTINCT ]
  { * | projectItem [, projectItem ]* }
  FROM tableName, UNNEST(tableName.subTableName)

Examples

1. Return the required fields of a child table.

SELECT
    field_int32,
    field_int32 + 1 as output,
    sub_id,
    sub_string
FROM
    simple4,
    UNNEST(simple4.sub_simple4_table)
WHERE
    field_int8 >= 2

2. Filter the fields of a child table.

SELECT
    field_int32,
    field_int32 + 1 as output
FROM
    simple4,
    UNNEST(simple4.sub_simple4_table)
WHERE
    field_int8 >= 2
    AND sub_id <= 5

Note:

If a WHERE clause contains the conditions of child table fields and the child table record that corresponds to a parent table record is empty in the query result, the parent table record is not returned by default.

If a WHERE clause does not contain the conditions of child table fields, a left join operation is performed on primary table records and child table records in the query result by default. You can use a hint to change the join method. For more information, see Hint.

If you configure primary tables and child tables, you can separately query the parent table.