MAPLOOKUP working in select clause but not in where clause

nikhil_viznikhil_viz Registered User

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!!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file