How to skip null records in Orientdb while using group by clause

Question

When I execute following query:

SELECT count(*) as count, $new_source as name 
from news 
LET $new_source = if(eval("source.indexof('Other') === 0"), "Other", source)
where country_id = "111111"
group by $new_source

it throws an error saying:

com.orientechnologies.orient.core.exception.OCommandExecutionException: expression item 'source' cannot be resolved because current record is NULL

It works well if there is at least one record for given country_id in the "news" class, but if there is no record for given country_id then it is throwing this error.

As I am using generic query for all news records irrespective of country_id, I want empty record set should be return if no record is there for specific country.

I have also tried with using orientdb's ifnull function to skip null values, something like this:

SELECT count(*) as count, $new_source as name from news LET $new_source = ifnull(source, 0, if(eval("source.indexof('Other') === 0"), "Other", source)) where country_id = "111111" group by $new_source

but it is not working, and throwing the same error.

I am using OrientDb 2.1.8. I don't want to use javascript function and call it from console (as suggested here)

Is there any way, I can skip null values while using if with group by?

Answer 1

I tried to modify your query and maybe I found a solution:

select count(*) as count,name
from (
  select if(eval("source.indexof('Other') === 0"), "Other", source) as name 
  from news where country_id = "111111")  
group by name

I have no errors and correctly no results.

select count(*) as count,name
from (
  select if(eval("source.indexof('Other') === 0"), "Other", source) as name 
  from news where country_id = "1110")  
group by name

Here correctly I have results

Hope it helps.

Answer 2

This should solve your problem:

remove your index and re-creates another index of type full-text with engine Lucene.

Querying you get this result: