Loading complex nested JSON to Vertica Felx tables and reading it

PravSaj

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

 

 

Ben_Vandiver

Easiest method might be to use the start_point parameter to the FJSONParser, which indicates a field which contains your records.  You could probably skip the flex table entirely and load directly into the base table.

 

See https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/FlexTables/FJSONPARSERreference.htm for details.