insert in one table get the ID and then include that id in next insert in Apache Nifi

[#1]

I need to inject data read from elastic in Json format to two different tables having parent child relationship.

i.e

• User table

Name, Age, Role
David,35,1

Tay,23,2

John,27,2

• Role table:

1,admin

2,user

My requirement is to insert in user table first get the Role ID and then include that id in next insert to dump data in Role table

How can i achieve this thing Apache Nifi?@mburgess

[#2]

@Adyant001    Can you share sample json structure ?

If source json contains both parent and child information, you can have two PutDatabaseRecord processors connected in series with right "Data Record Path" mentioned.

If child record needs information from parent level, JoltTransformJson processor can be used to create transformed json before passing to PutDatabaseRecord.

[#3]

[

{

"name": "sugam",

"age": 30,

"id": 1,

"role": {

"role": "Admin"

}

}

]

I wanted to put name ,age, and id to user table then get the id from user table and put it in role table with role as admin

Desired out put should be as below

user table:

Name  age ID(pk)

sugam 30   1

Role Table:

Id(pk)  Role_id(fk) Role_name      

1                1                       admin   

@mburgess @SAMSAL 

[#4 · Accepted solution]

I would do this way.  Please see if this works for you.

1. Use JolttransformJSON processor to alter the input JSON

Spec:

[
{
"operation": "shift",
"spec": {
"id": ["role.role_id", "user.id"],
"name": "user.name",
"age": "user.age",
"role": {
"role": "role.role_name"
}
}
}
]

2. Pass the transformed JSON to first PutDatabaseRecord processor to insert into user table

      Set "Data Record Path"  = user

3.  Add another PutDatabaseRecord processor to insert into role table

       Set "Data Record Path"  = role

[#5]

HI Shiva239.

The solution seems working for me but the child table(role) has an autoincrement PK column.

SO how to insert value in autoincrement column

Role Table:

Id(pk auto increamne)  Role_id(fk) Role_name      

1                                                    1                       admin   

[#6]

@Adyant001 

Oracle has auto increment IDENTITY feature as below.

Table definition takes care of setting primary key incremental value. No need to send this value in json payload

I think all other major RDBMS have similar feature to assign auto increment value.

CREATE TABLE roletab
(id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
(START WITH 1 INCREMENT BY 1 NOCYCLE),
role_id NUMBER,
role_name VARCHAR(100),
PRIMARY KEY (id)
);

INSERT INTO roletab (role_id, role_name) VALUES (10, 'Admin');
INSERT INTO roletab (role_id, role_name) VALUES (20, 'Developer');
commit;

SELECT * FROM roletab;

ID |ROLE_ID|ROLE_NAME|
--+-------+---------+
1    | 10     | Admin |
2   | 20    |Developer|