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

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 Employee

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

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.