The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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


  • 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 | | number | provider.address | | time
    myaddress | test1 | 438342932 | myaddress | test2 | 2008-12-10
    (1 row)

  • 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.address", "", "provider.address". It should load into defined columns that way.

  • 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?

  • 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