Loading complex nested JSON to Vertica Felx tables and reading it



I have a complex nested JSOn file which I was able to load it to Flex table and read it. I have now have a requirement to comvert my nested JSON into Multiple rows and load it to a table.


The data from the attached JSON tables to be loaded to the following table.

Create Table VehicleResultExtras (
PropositionId Varchar(36),
enquiryId Varchar(36),
ExtraTypeID Int,
Description Varchar(100),
IsProductFeatureIncluded Boolean,
IsQuoteExtrasStatic Boolean,
ProductFeaturePrice Decimal(9,2),
CreatedDateTime Timestamp


I have loaded my JSON data to a flex table like below.

create Flex Table VehicleResultExtras_tmp();

copy VehicleResultExtras_tmp
From Local 'C:\Vehicle Results\TestData\results.json' parser fjsonparser();


I am able to read data from first nest as below:

Cast(maplookup(maplookup(__raw__, 'body.results.results'), '0.resultId') As Varchar(50)) As PropositionId,
CAST("body.results.enquiryId" As Varchar(36)) As EnquiryID,
Cast(maplookup(maplookup(maplookup(__raw__, 'body.results.results'), '0.quote.extras'),'0.extraTypeId') As Int) As ExtraTypeID,
Cast(maplookup(maplookup(maplookup(__raw__, 'body.results.results'), '0.quote.extras'),'0.extraTypeId') As Varchar(100)) As Description,
Cast(maplookup(maplookup(maplookup(__raw__, 'body.results.results'), '0.quote.extras'),'0.extraTypeId') As Boolean) As IsProductFeatureIncluded,
Cast(maplookup(maplookup(maplookup(__raw__, 'body.results.results'), '0.quote.extras'),'0.extraTypeId') As Boolean) As IsQuoteExtrasStatic,
Cast(maplookup(maplookup(maplookup(__raw__, 'body.results.results'), '0.quote.extras'),'0.extraTypeId') As Decimal(9,2)) As ProductFeaturePrice,
Cast(maplookup(maplookup(__raw__, 'body.results.results'), '0.created') As Timestamp) As CreatedDateTime
From VehicleResultExtras_tmp


How ever I want to read data from all "periods" from my felx table and load it to my table with out manually changing my query from period 0* to 1.*etcc.


Is there any easy way to achcive this?







Leave a Comment

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