How to filter using ksql with array attribute type

Question

I have a Stream on a topic with schema:

--root
  --name: string
  --age: integer
  --accounts: Array
    --email

I would like to select all root elements having accounts with email ='xx'

Thanks you for help.

Answer 1

select * from t1 where accounts->email='xx'

Assumption table or stream name is t1.

Answer 2

In general case a new stream with exploded array elements must be created

create stream ExplodedStream
with(kafka_topic='ExplodedTopic')
as
select
  root->name,
  root->age,
  explode(root->accounts) as account
from OriginStream

This stream will explode one message with multiple accounts into multiple messages with one account inside. Messages will be stored in a new ExplodedTopic topic. In your case streams and topics names will be other. This topic messages can be easily filtered as

select * 
from ExplodedStream 
where account->email='xx'

If number of accounts are known then filtering can be done without creating a new stream. Just filter by all known account elements

select * 
from OriginStream 
where 
  root->accounts[1]->email='xx'
  or root->accounts[2]->email='xx'
  ...
  or root->accounts[n]->email='xx'

Number of array element is one based (1,2,3,...)