Unable to load Json data to Vertica Flex Table



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.






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

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




  • 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)

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

  • Hi!


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

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

  • That works beautifully..you are a star!!

Leave a Comment

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