Parse json file with Arrays

mani5b8mani5b8 Registered User
edited October 25 in Vertica Forum

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?

Comments

  • chaimachaima Employee, Registered User

    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

  • Ariel_CaryAriel_Cary Employee, Registered User, VerticaExpert

    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)
    
  • mani5b8mani5b8 Registered User

    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?

  • mani5b8mani5b8 Registered User

    @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

  • Ariel_CaryAriel_Cary Employee, Registered User, VerticaExpert

    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)
    
  • mani5b8mani5b8 Registered User

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

  • mani5b8mani5b8 Registered User

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

  • Ariel_CaryAriel_Cary Employee, Registered User, VerticaExpert

    Could you tell us what Vertica version you are using?

  • mani5b8mani5b8 Registered User

    Vertica Analytic Database v9.1.1-8

  • Ariel_CaryAriel_Cary Employee, Registered User, VerticaExpert

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

Leave a Comment

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