How do I use WHERE clause inside of an array of objects

Open Q & A
1

I have a collection that has a field like this

psel: [
   {
      s: "xxxxx",
      l: "xxxxx"
   },
   {
      s: "xxxxx",
      l: "xxxxx"
   }
]

I wish to do this query

SELECT
  COUNT(*)
FROM
  database
WHERE
  pSel.s = "xxxx"

Any ideas on how to do this, rockset collection saids that this field pSel[*].s is available but when I do queries using that field it throws an error

Thanks

2

There are a couple of different ways to do this. Here’s one way:

SELECT
  COUNT(*)
FROM
  database d
WHERE
  EXISTS (SELECT 1 FROM UNNEST(d.psel) as p WHERE p.s = 'xxxx')

Note: you need to use single quotes for strings such as ‘xxxx’ and not double-quotes.

3

Thank you very much!

4

I would like to know whats the other way to this, or if there is a faster way to do it

5

Can you share the exact query you are trying along with what performance you are seeing on it and what Virtual Instance are you using at the moment?