jsonPath Expression for json and json of json parameter using NIFI expression Langauge

Question

I have json like below,

{
    "name1" : "Aks",
    "address1": {
      "area" : "area1",
      "location1": "loc1"

    },
    "phonenum": "1445452"
}

I need to retrieve all keys and from address1 only location1 parameter.

I tried, $.*, $.['address1'].location1 in JsonPath Expression, but didn't succeed, any hint on this ?

please explain better.. Do you need to extract `name1`, `phonenum`, and `address1.location1` into to their own attributes? — Ben Yaakobi, Apr 23 '20 at 06:07

score 1 · Answer 1 · answered Apr 23 '20 at 12:01

1

@aksy91 you need to use the EvaluateJsonPath Processor for the mapping:

name1: $.name1

location1: $.address1.location1

phonenum: $.phonenum

Be sure to set the first value to flowfile-attribute.

answered Apr 23 '20 at 12:01

steven-matison

Yes, Worked! Thank you, One more clarification needed, Can insert this flow-file attributes to local db table using PutDatabaseRecord Prcessor..? My findings, ExecuteSQL-->ConvertAvroToJson-->EvaluateJson-->PutDatabaseRecord. But In PutDatabaseRecord -- > selected JSONTreeRecord as RecordReader and AvroSchemaRegistry as its SchemaAccessStrategy. I have oracle DB, I am using 'username' as the 'schema.name', it is failing to insert with error failed to process standardflowfilerecord nifi putdatabaserecord.. Please provide any suggestions on this.. – aksy91 Apr 23 '20 at 13:47

Ben Yaakobi · Answer 2 · 2020-04-23T07:46:58.060

0

Well, what you need to do is to add three new dynamic attributes:

name1: $.name1

location1: $.address1.location1

phonenum: $.phonenum

edited Apr 23 '20 at 07:46

answered Apr 23 '20 at 07:22

Ben Yaakobi

In same jsonPath Expression ? Any delimiter should be used in the expression Path ? example : $.name1,$.address1.location1,$.phonenum – aksy91 Apr 23 '20 at 07:23
1

just enter it as is, `attribute_name`: `attribute_value` . Of course, `Destination` should be set to `flowfile-attribute` too. Please tell me if it works for you – Ben Yaakobi Apr 23 '20 at 07:26
Used as is you mentioned, but no luck.. Where I am going wrong, unable to understand – aksy91 Apr 23 '20 at 07:34
1

please add to your question the full details with screen shots included of what you've tried to perform, I'll add screenshots of what's I've done as well – Ben Yaakobi Apr 23 '20 at 07:44
I have structure like this, ExecuteSQL --> ConverAvroToJson --> UpdateAttribute --> SplitJson So in SplitJson I am giving jsonPath Expression like below name1: $.name1 location1: $.address1.location1 phonenum: $.phonenum I am doing it correct ? – aksy91 Apr 23 '20 at 08:01

2 Answers2