CQL query to get count of elements in LIST DATATYPE of CassandraDB?

So there isn't a standard way in Cassandra to accomplish this. The easiest way would be to SELECT the list from the table and pull a count of its items on the application side.

That being said, there is a way to accomplish this by building a user defined function (UDF). First of all, UDFs are disabled by default with Cassandra, to prevent execution of malicious code by users of a cluster. If you're ok with enabling them, you'll find that option in the cassandra.yaml file. It's a simple boolean, so you'll want to set it to true:

enable_user_defined_functions: true

Of course, you'll have to stop/restart the cluster for this change to take effect.

Next, you can create a UDF to return an integer representing the size of the list, like this:

CREATE OR REPLACE FUNCTION countlist (input List<text>)
    RETURNS NULL ON NULL INPUT RETURNS int
    LANGUAGE java AS 'return input.size();';

Essentially what this does, is the List<text> Cassandra type gets mapped to a java.util.List<String> Java type. From there, it simply invokes the size() method on the list.

Now, I can use that countlist function with CQL. So let's say I have a table to keep track of parents' children:

CREATE TABLE kids (
    parent text PRIMARY KEY,
    children list<text>);

For me, I can query it like this:

SELECT parent,countlist(children) FROM kids WHERE parent='Aaron';

 parent | stackoverflow.countlist(children)
--------+-----------------------------------
  Aaron |                                 4

(1 rows)