…
“sns”: [
{“sn”: “sn1”},
{“sn”: “sn2”},
…
]
The array is big. When I tried to use UNNEST to query:
select count(*) as sn_count from myCollection a, UNNEST(sns AS value) AS b
where b.value.sn like ‘sn10000000%’
I got an error:
This query consumed 100MB of memory, which exceeds the limit of 100MB…
I am using an evaluation account for now so likely the limit is 100M, but even later if I use a real account I assume there will be still a limit.
What could I do to make it work for a very large array? Any help will be appreciated.
It appears you hit the Query Memory Limit. For shared Virtual Instances, each individual query has a memory limit of 100 MB. On dedicated Virtual Instances, the memory limit can be increased. Note that the memory available for your queries are determined by the size of the Virtual Instance and is shared by all concurrent queries in your account. So hitting some memory limits could be a result of sending too many expensive queries.
This limit is placed on the memory used for storing transient data in between steps of the query execution, so the trick to avoid this error is effective query rewriting! Check out our Rockset query tuning page for tips on how to improve the SQL.
In your query, the UNNESTed value is only being used as a filter in the WHERE clause. Instead of running an expensive UNNEST, you can avoid UNNESTing by using ARRAY_CONTAINS in the WHERE clause instead.