Multi Value dimension query

Hi,

I am trying to querying AND condition on Multi Value dimension.

Attached sample data file (IMDB) and ingestion spec json.

The following SQL works as expected.

select * from imdb_sample where primaryProfession = ‘actor’ – returns 9 records

But, i wanted to query all rows where profession is ‘actor’ and ‘director’ and ‘producer’. The following query returns 0 records.

select * from imdb_sample where primaryProfession = ‘actor’ and primaryProfession = ‘director’ and primaryProfession = ‘producer’

I tried multiple options using MV_OVERLAP(), but i am not able to get the expected result.

I also tried creating primaryProfession_2 (duplicate, at the ingestion time) and query using primaryProfession = ‘actor’ and primaryProfession_2 = ‘director’.

Even this did not work.

Is this scenario possible to solve? Please help.

Regards, Chari.

imdb_sample.csv (1.34 KB)

imdb_ingestion.json (1.23 KB)

I don’t think it’s supported yet: https://github.com/apache/incubator-druid/issues/7525

Thanks

Thanks for your response Ming.

In the 2nd SQL i mentioned, i have 2 dimensions with same values in each. Ideally the query should work for multi value, as mentioned in the SQL. But Druid is giving 0 results.

Is there anyway to achieve this?

Regards, Chari.

Seems dsq has got some limitation while querying multivalued dimensions, Could you try native JSON query as below and see if you get expected result :
{
“queryType”: “select”,
“dataSource”: “DATASOURCENAME”,
“descending”: “false”,
“dimensions”:,
“metrics”:,
“granularity”: “all”,
“intervals”: [
“0000-01-01/9999-01-02”
],

“filter”:{
“type”: “and”,
“fields”: [
{
“type”: “selector”,
“dimension”: “primaryProfession”,
“value”: “actor”
},
{
“type”: “selector”,
“dimension”: “primaryProfession”,
“value”: “producer”
},

{
“type”: “selector”,
“dimension”: “primaryProfession”,
“value”: “director”
}
]
}
“pagingSpec”:{“pagingIdentifiers”: {}, “threshold”:5}
}

Thanks,

Vaibhav

Hi Vaibhav,

Thanks, the native JSON query works.

I found an alternate in SQL, the following works for the same use case.

select * from imdb_sample_multivaluedimension where

MV_TO_STRING(primaryProfession,’-’) like ‘%actor%’ and

MV_TO_STRING(primaryProfession,’-’) like ‘%producer%’ and

MV_TO_STRING(primaryProfession,’-’) like ‘%director%’

Regards, Chari.

Good to hear that .

Thanks,

Vaibhav

Hi Lakshminarayana,

The druid query that you mentioned will not work if primaryProfession column contained integer values and we needed to use any relational operator(>, <). Any workarounds for that?