Parse json file with Arrays
Hi,
I have a json file with below structure
{"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"
}]
}
}
Expected Output:
311/A|Magure Square|London|456112
4112-B|Madison Square|Manchester|125465
I tried using a json parser I found developed in C which is not able to handle above request.
Can someone help me parse this json without flex tables?
0
Comments
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');
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.
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.
}
}
=> 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();