Nested field query improvement

Is your feature request related to a problem? Please describe.
Nested field query is supported by following PartiQL specification: https://github.com/opensearch-project/sql/blob/main/docs/user/beyond/partiql.rst. There are limitations regarding the nested levels and query types that needs improvement.

1. https://github.com/opendistro-for-elasticsearch/sql/issues/1055: Comparison between nested field
2. https://github.com/opendistro-for-elasticsearch/sql/issues/963: ?
3. https://github.com/opendistro-for-elasticsearch/sql/issues/797: Nested field involved in join
4. https://github.com/opendistro-for-elasticsearch/sql/issues/399: Select *
5. https://github.com/opendistro-for-elasticsearch/sql/issues/398: Aggregate over nested field
6. https://github.com/opendistro-for-elasticsearch/sql/issues/245: Nested field involved in join
7. https://github.com/opendistro-for-elasticsearch/sql/issues/155: Aggregate over nested field
8. https://github.com/opendistro-for-elasticsearch/sql/issues/152: Deep nested field
9. https://github.com/opendistro-for-elasticsearch/sql/issues/87: Filter nested field

Describe the solution you'd like
N/A

Describe alternatives you've considered
N/A

Additional context
Add any other context or screenshots about the feature request here.

Start with the PartiQL specification linked in the issue, then review the nine related issues covering nested comparisons, joins, aggregation, selection, filtering, and deep nesting. Define the specific nested query types and nesting levels to improve before identifying the relevant implementation and tests; the issue does not name files or an acceptance criterion.