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!

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