MAPLOOKUP working in select clause but not in where clause
Hi,
I am currently using Vertica Analytic Database v7.1.1-0. I have a column named impression which stores jsonObject as string eg-
{
"push": {
"bannerID": "|44123|1131762|123123|"
}
}
Query1:
select TO_CHAR(MAPLOOKUP(MapJSONExtractor(impression),'push.bannerID')) from "VIZVRM4485_ecsegmentation" where impression is not null;
Query2:
select count(*) as "count" from "VIZVRM4485_ecsegmentation" where impression is not null
and TO_CHAR(MAPLOOKUP(MapJSONExtractor(impression),'push.bannerID')) like '%bb%'
Running MAPLOOKUP in query 1 gives me expected result but MAPLOOKUP in query2 gives error.
[Code: 5861, SQL State: VP001] [Vertica]VJDBC ERROR: Error calling processBlock() in User Function MapJSONExtractor at [src/JSONParser.cpp:958], error code: 0, message: Unrecoverable parse exception while processing partition: [Unrecoverable YAJL Error on record # [1] with status: [parse error] and [1] Bytes consumed: [lexical error: invalid char in json text.
Please Help!!