Glenn Justo Galvizo created ASTERIXDB-3059:
----------------------------------------------
Summary: EXCEPT in SelectClause
Key: ASTERIXDB-3059
URL: https://issues.apache.org/jira/browse/ASTERIXDB-3059
Project: Apache AsterixDB
Issue Type: New Feature
Components: SQL - Translator SQL++
Reporter: Glenn Justo Galvizo
Assignee: Glenn Justo Galvizo
Attachments: SelectClause-1.png
It would be nice to have an option to exclude certain fields when using SELECT
*.
[BigQuery|https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_except]
gives users the option to exclude columns with 'SELECT * EXCEPT f1, f2, ...'.
'EXCEPT' seems like a nice addition to our grammar, here are a few examples on
how we can extend our SELECT clause to include 'EXCEPT':
1. Get me all users U1 and their friends U2. Do not include the intermediate F
dataset. In the example below, we have a 'SELECT *' followed by an 'EXCEPT ( F
)'. In contrast to BigQuery, we do not specify columns / fields in our
exclusion list here, instead we specify an exclusion of _variables_ that would
have otherwise been returned with our 'SELECT' clause.
{code:sql}
FROM Users U1, Friends F, Users U2
WHERE U1.user_id = F.user_id AND
F.friend = U2.user_id
SELECT *
EXCEPT F;{code}
2. Get me all users U1 that have friends, and their friend's IDs. Do not
include that user's last name and address. The example below more closely
aligns with BigQuery, as we specify columns / fields that we want to exclude
from U1 in the exclusion list 'last_name, address'.
{code:sql}
FROM Users U1, Friends F, Users U2
WHERE U1.user_id = F.user_id AND
F.friend = U2.user_id
SELECT U1.*,
U2.user_id AS friend_user_id
EXCEPT last_name, address;{code}
3. If we are only interested in users U1 that have friends, then we can use an
'EXCEPT' on a 'SELECT VALUE'.
{code:sql}
FROM Users U1, Friends F, Users U2
WHERE U1.user_id = F.user_id AND
F.friend = U2.user_id
SELECT DISTINCT VALUE U1
EXCEPT last_name, address;{code}
4. If we want to exclude some field in a nested object, we can specify the
fields within our object with the '.' syntax. Say we don't want to include the
street code of an address object within a user that has friends. We would use
the query below:
{code:sql}
FROM Users U1, Friends F, Users U2
WHERE U1.user_id = F.user_id AND
F.friend = U2.user_id
SELECT DISTINCT VALUE U1
EXCEPT address.zip_code;{code}
The exact grammar I have in mind is given below, where we simply add the
'EXCEPT' at the tail of our 'SELECT' clause. Logically, the 'EXCEPT' clause
surrounds our SELECT clause expression with a call to OBJECT_REMOVE_FIELDS.
Using #3 as an example, a logical equivalent would be:
{code:sql}
FROM Users U1, Friends F, Users U2
WHERE U1.user_id = F.user_id AND
F.friend = U2.user_id
SELECT DISTINCT VALUE OBJECT_REMOVE_FIELDS(U1, ["last_name", "address"]);{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
