Options
to write nested json attributes in standart table
maks
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
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?