SQL WHERE clause on arrays

[Simon_Duchene_lenses]

Hi There,
i’m copy/pasting a question from Anand on our Community slack that is valuable for others :

I have a challenge to run SQL query with “Where” clause. The Kafka topic is a list of arrays.

{
  "value": [
    {    {
      "VIN": "1XXXXXXXXXXXXXX22485",
      "MESSAGE_ID": "5XXXXXXXXXb7ac",
      "STATUS": "Y"
    },
    {
      "VIN": "1XXXXXXXX5",
      "MESSAGE_ID": "5XXXXXXc4b7ac",
      "STATUS": "Y"
    }
 ]
}

Any working examples is appreciated?

USE kafka;
SELECT * FROM MyInput
WHERE VIN=‘1XXXXXXXXXXXXXX22485’;

---

Returns 0 row

[Simon_Duchene_lenses]

Hello Anand, I’d recommend you to use LATERAL to first explode the array (products) into new single field (singleproduct),
then use the WHERE clause to filter
for a data which has a products field that is an array, the SQL would be: see DOC

USE `kafka`;
SELECT * FROM mytopic
LATERAL products as singleproduct
where singleproduct.quantity>2
LIMIT 100;

before : fields in array

after : field exploded in multiple events with a single field singleproduct, you can filter with where :

I hope this helps!