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