Skip to main content
Question

to write nested json attributes in standart table

  • May 21, 2020
  • 4 replies
  • 11 views

maks
Forum|alt.badge.img+1
  • Participating Frequently

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

4 replies

Bryan_H
Forum|alt.badge.img+2
  • Participating Frequently
  • May 21, 2020

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
Forum|alt.badge.img
  • Participating Frequently
  • May 21, 2020

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
Forum|alt.badge.img+1
  • Author
  • Participating Frequently
  • May 22, 2020

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
Forum|alt.badge.img
  • Participating Frequently
  • May 22, 2020
See my prior post. Flatten maps and define the columns with the dot notation as indicated.