What to do if you want to delete rows?
Use UNNEST -
Error: Cannot access field hitId on a value with type ARRAY< STRUCT< hitId STRING, isEntrance INT64, isExit INT64, ...>>
delete
FROM
xxxx.session_streaming_20161029
WHERE
h.hitId = (
SELECT hitId
FROM xxxx.session_streaming_20161029, UNNEST(hits) as h
WHERE h.transaction.transactionId = '123456')
If I understand correctly, I think that you actually want:
delete FROM xxxx.session_streaming_20161029
WHERE EXISTS (
SELECT 1
FROM xxxx.session_streaming_20161029,
UNNEST(hits) as h
WHERE h.transaction.transactionId = '123456');
The idea is to match all rows where there is some transaction ID with value '123456'.
Edit: If the goal is to remove entries from hits with this transaction ID, and not to remove entire rows where hits contains the transaction ID, you can use UPDATE:
UPDATE xxxx.session_streaming_20161029
SET hits =
ARRAY(SELECT hit FROM UNNEST(hits)
WHERE transaction.transactionId = '123456');
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments