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

Answers

  • 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();
    CREATE TABLE
    dbadmin=> COPY forumJson FROM LOCAL '/tmp/forum.json' PARSER FJSONPARSER();
    Rows Loaded
    -------------
    1
    (1 row)
    dbadmin=> SELECT COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW('forumJson');
    COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW
    --------------------------------------------------------------------------------------------------------
    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;
    CREATE TABLE
    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)

  • 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.name", "executor.address", "provider.name", "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
Emoji
Image
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.