The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

MAPLOOKUP working in select clause but not in where clause

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

select TO_CHAR(MAPLOOKUP(MapJSONExtractor(impression),'push.bannerID')) from "VIZVRM4485_ecsegmentation" where impression is not null;

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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file