to write nested json attributes in standart table

maks

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_H

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)

DaveT

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.

maks

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?

DaveT

See my prior post. Flatten maps and define the columns with the dot notation as indicated.