Unable to load Json data to Vertica Flex Table

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?

Thanks,

Prav

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;
 maplookup
-----------
 T
 T
(2 rows)

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

That works beautifully..you are a star!!