The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Loading complex nested JSON to Vertica Felx tables and reading it

Hi,

 

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();
go

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

--------------------------------------------------------------------------

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

Select
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?

 

Tx

Prav

 

 

Comments

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.