to write nested json attributes in standart table

maksmaks Community Edition User

Hello. I have messages in json format with nested structure like:
"time": "2008-12-10",
"number": "438342932",
"executor": {
"name": "test1",
"address": "myaddress"
"provider": {
"name": "test2",
"address": "myaddress"

I can write these messages in flex table but I want write these messages in standart table - simple json attributes such as time, number are written without problems in standart table but I don't understand how to write nested attributes name and address


  • Options
    Bryan_HBryan_H Vertica Employee Administrator

    The quickest way is to load JSON into a flex table and then create a regular (ROS) table from that.
    I copied your sample JSON to '/tmp/forum.json' and created a regular table as follows:
    dbadmin=> create flex table forumJson();
    dbadmin=> COPY forumJson FROM LOCAL '/tmp/forum.json' PARSER FJSONPARSER();
    Rows Loaded
    (1 row)
    Please see public.forumJson_keys for updated keys
    The view public.forumJson_view is ready for querying
    (1 row)
    dbadmin=> CREATE TABLE forumJson_ROS AS SELECT * FROM forumJson_view;
    dbadmin=> SELECT * FROM forumJson_ROS;
    executor.address | executor.name | number | provider.address | provider.name | time
    myaddress | test1 | 438342932 | myaddress | test2 | 2008-12-10
    (1 row)

  • Options
    DaveTDaveT Vertica Employee Employee

    If you want to predefine columns in a flex table or regular table one way is to use flatten_maps=true (it is the default) with fjsonparser. In your example, define your column names such as "executor.name", "executor.address", "provider.name", "provider.address". It should load into defined columns that way.

  • Options
    maksmaks Community Edition User

    I want not to use the flex table, only the standard one - to load data directly into it from json. Is it possible for nested json attributes name and address?

  • Options
    DaveTDaveT Vertica Employee Employee
    See my prior post. Flatten maps and define the columns with the dot notation as indicated.

Leave a Comment

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