Parse json file with Arrays

Hi,

If I understood your problem correctly, you can achieve this using fjsonparser to load your data in a regular columnar table.
Here is an example (btw there is a missing comma in the file otherwise it's not a valid json):
dbadmin=> ! cat /tmp/json_table.json
{"Event":{"name":"John",
"Age":"29",
"Address":[{"Addr1":"311/A",
"Addr2":"Magure Square",
"City":"London",
"Zip":"456112"
},
{"Addr1":"4112-B",
"Addr2":"Madison Square",
"City":"Manchester",
"Zip":"125465"
}]
}
}
dbadmin=> CREATE TABLE json_table(addr1 varchar(32), addr2 varchar(32), city varchar(32), zip integer);
CREATE TABLE
dbadmin=> copy json_table from '/tmp/json_table.json' WITH PARSER fJsonParser(start_point='Address');

dbadmin=> select * from json_table;
 addr1  |     addr2      |    city    |  zip
--------+----------------+------------+--------
 311/A  | Magure Square  | London     | 456112
 4112-B | Madison Square | Manchester | 125465
(2 rows)

Let me know if this helps,
Chaima

You can use FJSONParser and tell the parser that you want to start parsing at a certain key, in your case 'Address' (the array that has the collection of addresses). FJSONParser equally loads data to columnar tables, not only to Flex tables. See the example below.

create table t2(Addr1 varchar, Addr2 varchar, City varchar, Zip varchar);
CREATE TABLE

-- Note the use of start_point to tell the parser where to start loading data.
-- Also unset flatten_maps to preserve nested data structure.
copy t2 from stdin parser fjsonparser(flatten_maps=false, start_point='Address');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.


  
    {"Event":{"name":"John",
  


 "Age":"29",
 "Address":[{"Addr1":"311/A",
 "Addr2":"Magure Square",
 "City":"London",
 "Zip":"456112"
 },
 {"Addr1":"4112-B",
  "Addr2":"Madison Square",
  "City":"Manchester",
  "Zip":"125465"
 }]
         }
}


  
    
      
        
          
            
              
                
                  
                    
                      
                        
                          
                            
                              
                                
                                  
                                    
                                      
                                        
                                          
                                            
                                              
                                                
                                                  
                                                    
                                                      
                                                        .
                                                      
                                                    
                                                  
                                                
                                              
                                            
                                          
                                        
                                      
                                    
                                  
                                
                              
                            
                          
                        
                      
                    
                  
                
              
            
          
        
      
    
  


select * from t2;
 Addr1  |     Addr2      |    City    |  Zip
--------+----------------+------------+--------
 311/A  | Magure Square  | London     | 456112
 4112-B | Madison Square | Manchester | 125465
(2 rows)

Hi Chaima,
Thanks for the help. its working great

But the issue is the table is not loaded with all the rows.
Only the array present in the first row of json file are loaded to table.

Below is the actual data:
{"Event":{"name":"John",
"Age":"29",
"Address":[{"Addr1":"311/A",
"Addr2":"Magure Square",
"City":"London",
"Zip":"456112"
},
{"Addr1":"4112-B",
"Addr2":"Madison Square",
"City":"Manchester",
"Zip":"125465"
}]
}
}
{"Event":{"name":"Russell",
"Age":"29",
"Address":[{"Addr1":"311/A",
"Addr2":"Magure Street",
"City":"Liverpool",
"Zip":"456345"
},
{"Addr1":"42-B",
"Addr2":"Man Square",
"City":"Manchester",
"Zip":"125789"
}]
}
}

Records of Russell are not getting loaded to the table. Is there anything I can do run the parser for all the records in the file?

@Ariel_Cary I have tried your approach as well. its working well.
But the elements of second row are not getting inserted to table.

There is a new line char at the end of every record

mani5b8: Could you show us an example of what you mean by second row?
I successfully loaded the two JSON records you posted in your previous example, i.e. 4 rows in total (ZIP codes are different).

truncate table t2;
TRUNCATE TABLE

copy t2 from stdin parser fjsonparser(flatten_maps=false, start_point='Address');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.


  
    {"Event":{"name":"John",
  


"Age":"29",
"Address":[{"Addr1":"311/A",
"Addr2":"Magure Square",


  
    
      
        
          
            
              
                "City":"London",
              
            
          
        
      
    
    
    "Zip":"456112"
  


},
{"Addr1":"4112-B",
"Addr2":"Madison Square",
"City":"Manchester",
"Zip":"125465"
}]
}
}
{"Event":{"name":"Russell",
"Age":"29",
"Address":[{"Addr1":"311/A",


  
    
      
        
          
            
              
                
                  
                    
                      
                        
                          
                            
                              
                                
                                  
                                    
                                      
                                        
                                          
                                            
                                              
                                                "Addr2":"Magure Street",
                                              
                                            
                                          
                                        
                                      
                                    
                                  
                                
                              
                            
                          
                        
                      
                    
                  
                
              
            
          
        
      
    
  


"City":"Liverpool",
"Zip":"456345"
},
{"Addr1":"42-B",
"Addr2":"Man Square",
"City":"Manchester",
"Zip":"125789"
}]
}
}


  
    
      
        
          
            
              
                
                  
                    
                      
                        
                          
                            
                              
                                
                                  
                                    
                                      
                                        
                                          
                                            .
                                          
                                        
                                      
                                    
                                  
                                
                              
                            
                          
                        
                      
                    
                  
                
              
            
          
        
      
    
  


select * from t2;
 Addr1  |     Addr2      |    City    |  Zip
--------+----------------+------------+--------
 311/A  | Magure Square  | London     | 456112
 4112-B | Madison Square | Manchester | 125465
 311/A  | Magure Street  | Liverpool  | 456345
 42-B   | Man Square     | Manchester | 125789
(4 rows)

@Ariel_Cary i ran the same query. But i am able to insert only first 2 records.

   => copy t2 from stdin parser fjsonparser(flatten_maps=false, start_point='Address');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

{"Event":{"name":"John",
"Age":"29",
"Address":[{"Addr1":"311/A",
"Addr2":"Magure Square",
"City":"London",
"Zip":"456112"
},
{"Addr1":"4112-B",
"Addr2":"Madison Square",
"City":"Manchester",
"Zip":"125465"
}]
}

}

{"Event":{"name":"Russell",

"Age":"29",
"Address":[{"Addr1":"311/A",
"Addr2":"Magure Street",
"City":"Liverpool",
"Zip":"456345"
},
{"Addr1":"42-B",
"Addr2":"Man Square",
"City":"Manchester",
"Zip":"125789"
}]

}

}

.

=> select * from t2;
 Addr1  |     Addr2      |    City    |  Zip
--------+----------------+------------+--------
 311/A  | Magure Square  | London     | 456112
 4112-B | Madison Square | Manchester | 125465
(2 rows)

I want to load the same data present in a file.
the the parser is copying the contents of first record only.

@Ariel_Cary I tried loading the same as you mentioned.

=> copy t2 from stdin parser fjsonparser(flatten_map
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

{"Event":{"name":"John",
"Age":"29",
"Address":[{"Addr1":"311/A",
"Addr2":"Magure Square",
"City":"London",
"Zip":"456112"
},
{"Addr1":"4112-B",
"Addr2":"Madison Square",
"City":"Manchester",
"Zip":"125465"
}]
}

}

{"Event":{"name":"Russell",

"Age":"29",
"Address":[{"Addr1":"311/A",
"Addr2":"Magure Street",
"City":"Liverpool",
"Zip":"456345"
},
{"Addr1":"42-B",
"Addr2":"Man Square",
"City":"Manchester",
"Zip":"125789"
}]

}

}

.

=> select * from t2;
Addr1 | Addr2 | City | Zip
--------+----------------+------------+--------
311/A | Magure Square | London | 456112
4112-B | Madison Square | Manchester | 125465
(2 rows)

I need to load this from a file.
Parser is not loading only 2 records with file aswell.

Could you tell us what Vertica version you are using?

Vertica Analytic Database v9.1.1-8

That doesn't look like a correct Vertica version. Please double check:
select version();