The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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" : {
    "" : "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'), '') from start_json;
    (2 rows)

  • That works are a star!!

Leave a Comment

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