# SQL WHERE clause on arrays

**URL:** <https://ask.lenses.io/t/sql-where-clause-on-arrays/198>\
**Category:** Lenses SQL\
**Created:** [March 14, 2024, 3:09pm UTC](https://ask.lenses.io/t/sql-where-clause-on-arrays/198 "2024-03-14T15:09:07Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Simon\_Duchene\_lenses](https://dub1.discourse-cdn.com/flex017/user_avatar/ask.lenses.io/simon_duchene_lenses/32/104_2.png) [@Simon\_Duchene\_lenses](https://ask.lenses.io/u/Simon_Duchene_lenses)\
**Post date:** [March 14, 2024, 3:09pm UTC](https://ask.lenses.io/t/sql-where-clause-on-arrays/198/1 "2024-03-14T15:09:07Z")

</div>

Hi There,  
i’m copy/pasting a question from Anand on our [Community slack](https://launchpass.com/lensesio) that is valuable for others :

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

```auto
{
  "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

---

<div class="post-metadata">

**Author:** ![Simon\_Duchene\_lenses](https://dub1.discourse-cdn.com/flex017/user_avatar/ask.lenses.io/simon_duchene_lenses/32/104_2.png) [@Simon\_Duchene\_lenses](https://ask.lenses.io/u/Simon_Duchene_lenses)\
**Post date:** [March 14, 2024, 3:12pm UTC](https://ask.lenses.io/t/sql-where-clause-on-arrays/198/2 "2024-03-14T15:12:38Z")

</div>

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](https://docs.lenses.io/5.4/lenses-sql/streaming/laterals/#single-lateral-joins)

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

```

before : fields in array

 ![image (1)](https://europe1.discourse-cdn.com/flex017/uploads/lenses1/original/1X/65fbe74dd3b18d4d11ddebf353db68c6d230fde3.png)

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

 ![image](https://europe1.discourse-cdn.com/flex017/uploads/lenses1/original/1X/8acb4c0dd0e2c98ef5c20f76d780689bc8320575.png)

I hope this helps!