# Cratedb getting the count of NULL values excluding empty arrays

**URL:** <https://community.cratedb.com/t/cratedb-getting-the-count-of-null-values-excluding-empty-arrays/1245>\
**Category:** CrateDB\
**Created:** [October 5, 2022, 3:18pm UTC](https://community.cratedb.com/t/cratedb-getting-the-count-of-null-values-excluding-empty-arrays/1245 "2022-10-05T15:18:10Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [October 5, 2022, 3:18pm UTC](https://community.cratedb.com/t/cratedb-getting-the-count-of-null-values-excluding-empty-arrays/1245/1 "2022-10-05T15:18:10Z")

</div>

Hello crate community back here again. I have a question that is a bit tricky. So I am trying to get the count of “NULL” values. However an empty array is also considered NULL. I have empty arrays in my table. My question is in a table with millions of record is it possible to get a count of items where the actual value of the column is “NULL” without including an empty array?  
I tried

```auto
SELECT COUNT (*)
FROM "scp_service_transaction"."transactions_v2"
WHERE "itemPrices" IS NULL AND "itemPrices" != []
LIMIT 100;

```

But that returns

```auto
Error!

[Object, Object]

```

Maybe because there are to0 many records being processed? Running

```auto
SELECT COUNT (*)
FROM "scp_service_transaction"."transactions_v2"
WHERE "itemPrices" IS NULL
LIMIT 100;

```

Gives me a count that includes empty arrays.

Thanks!

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [October 5, 2022, 3:56pm UTC](https://community.cratedb.com/t/cratedb-getting-the-count-of-null-values-excluding-empty-arrays/1245/2 "2022-10-05T15:56:07Z")

</div>

Dear @arafaraf,

thank you for creating this issue. May I ask which version of CrateDB you are using?

I think there has been an improvement on this matter recently: [Ensure IS NULL does not match empty arrays by mfussenegger · Pull Request #12704 · crate/crate · GitHub](https://github.com/crate/crate/pull/12704). It should be available per CrateDB 5.x and the most recent 4.8.x release.

With kind regards,  
Andreas.

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [October 5, 2022, 3:58pm UTC](https://community.cratedb.com/t/cratedb-getting-the-count-of-null-values-excluding-empty-arrays/1245/3 "2022-10-05T15:58:36Z")

</div>

Thank you for your response. Sadly I am on crate Version:  
4.6.7. Wondering if there is any way around this on my current version since in prod I don’t think we will be updating versions.

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [October 6, 2022, 2:21pm UTC](https://community.cratedb.com/t/cratedb-getting-the-count-of-null-values-excluding-empty-arrays/1245/6 "2022-10-06T14:21:10Z")

</div>

Dear @arafaraf,

sadly there is no workaround for this problem, you will need [CrateDB Version 4.8.2](https://crate.io/docs/crate/reference/en/5.0/appendices/release-notes/4.8.2.html#fixes) or newer. Am I right to assume you are using a _primitive_ inner-type for your `itemPrices` array? \[1\]

With kind regards,  
Andreas.

* * *

1. While investigating the problem space, we discovered a flaw for the _non-primitive_ inner-types OBJECT and GEO\_SHAPE, see [Selecting empty arrays vs. NULL values when using non-primitive inner-types OBJECT and GEO\_SHAPE · Issue #13100 · crate/crate · GitHub](https://github.com/crate/crate/issues/13100#). I think it will not matter for your setup unless you are using such – I just wanted to check back about this detail with you.

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [October 6, 2022, 2:55pm UTC](https://community.cratedb.com/t/cratedb-getting-the-count-of-null-values-excluding-empty-arrays/1245/7 "2022-10-06T14:55:48Z")

</div>

Yes the type is “ARRAY(REAL)”

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [October 6, 2022, 3:43pm UTC](https://community.cratedb.com/t/cratedb-getting-the-count-of-null-values-excluding-empty-arrays/1245/8 "2022-10-06T15:43:57Z")

</div>

Thank you for your prompt response. With CrateDB \>=4.8.2, I think you can expect this issue to be fixed for your scenario. This quick snippet demonstrates it. Apologies that there is apparently no workaround.

```auto
docker run --rm -it --publish=4200:4200 crate:4.8.2

```

```auto
crash <<SQL
  DROP TABLE IF EXISTS testdrive;
  CREATE TABLE testdrive (details ARRAY(REAL));
  INSERT INTO testdrive (details) VALUES ([]);
  INSERT INTO testdrive (details) VALUES (NULL);
  REFRESH TABLE testdrive;
  SELECT count(*) AS count_array_is_null FROM testdrive WHERE details IS NULL;
  SELECT count(*) AS count_array_equals_empty FROM testdrive WHERE details = [];
SQL

```

```auto
+---------------------+
| count_array_is_null |
+---------------------+
| 1 |
+---------------------+
SELECT 1 row in set (0.004 sec)

+--------------------------+
| count_array_equals_empty |
+--------------------------+
| 1 |
+--------------------------+
SELECT 1 row in set (0.004 sec)

```