Unable to load Json data to Vertica Flex Table

PravSaj

Hi,

 

I am trying to load some really complex JSON data to a vertica flex table but for some reason the data is not getting load. I have two rows results.JSON file first row is slighly simple which is loading but second one which is slightly more complex is getting loaded as null. Cany you please help?

I am trying below.

 

create Flex Table Start_Json();
copy Start_Json From Local 'C:\Vehicle Results\results.json' parser fjsonparser();

SELECT maptostring(__raw__) FROM start_json;

 

I have attached my JSON file for your reference.

 

Thanks,

Prav

 

sKwa

Hi!

 

I successfully loaded both rows.

 

1. Can you post your environment?

2. Try to upload file on Vertica host(if you can) and do a COPY(not COPY LOCAL).

PravSaj

Hi,

 

Unfortunaterly I dont have access to upload file to the server so I am running from Local.

 

I am running my sql statements using Aquastudio connecting to Vertica database. Do you think running from local is the reason for error?

 

Thanks,

Prav

sKwa

Hi!

 

 

Hm... sorry, I just checked one more time its problem in maptostring function. Actually 2 rows loaded, but maptostring(__raw__) returns a second string as NULL.

 

You can validate it by yourself:

 

=> select eventVersion, eventId from start_json;
 eventVersion |               eventId               
--------------+--------------------------------------
 1.0.0        | 751569df-f71d-4610-8131-e89ec5b7a33a
 1.0.0        | e7f6273a-76dc-4426-a219-b4d5ca92aaf5
(2 rows)

 

 

=> SELECT row_number() over () ,  __raw__::varbinary(10) FROM start_json;
 ?column? |                 __raw__                 
----------+------------------------------------------
        1 | \001\000\000\000\332\021\000\000\016\000
        2 | \001\000\000\000\305^\001\000\016\000
(2 rows)

PravSaj

Hi Thanks a lot thats very useful but i have one more question on how can i extract data from nested json.

 

 

For example how can i extract data for "0.apply.desktop"

"body.results.results" : {
"0.apply.active" : "F",
"0.apply.desktop" : "T",

I tried select "body.results.results.0.apply.desktop" from start_json and it returns 2 rows with null value.

sKwa

Hi!

 

daniel=> select maplookup(maplookup(__raw__, 'body.results.results'), '0.apply.desktop') from start_json;
 maplookup
-----------
 T
 T
(2 rows)

daniel=> select maplookup(maplookup(__raw__, 'body.results.results'), '0.apply.active') from start_json;
 maplookup
-----------
 F
 T
(2 rows)

PravSaj

That works beautifully..you are a star!!