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

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
0
Answers
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)
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?