Nested JSON from relational database

[PEETERS Philippe <ph...@etnic.be>]

Hello,

I’m a newbie at nifi and I’m struggling at building a dataflow able to move data from a relational database to elasticsearch. I cannot find a simple way to build a structured JSON representation of a simple relational model record.
Let’s say the model is something like this:

ACTOR ---< CONTEXT ----< ATTRIBUTES
Each ACTOR can be attached to several CONTEXT and each context defines a set of attributes.

Initially, I’ve tried to build a nested JSON record reflecting that structure. Something like:

{ “ActorName”:”myname”,
  "ActorCreationDate":"1/2/2018",
  "Context" : [ {"name":"ctxname1",
                 "Attributes":[ {"key1":"val1"},{"key2","val2"} ]},
                {"name":"ctxname2",
                 "Attributes":[ {"key3":"val3"},{"key4","val4"},{"key5":"val5"} ]}
               ]
}

I didn't manage to find a way of enriching a JSON using a  SQL lookup (first the actor, then adding the contexts and the attributes for each of them) nor did I find how to structure a fully denormalized query.

Eventually, I manage to build the document in elasticsearch itself by updating sequentially the Actor document with its attributes. In the example above, I insert first a basic actor document for "myname" and then I update this document with the 5 (Context->Attr->key,value) sub-document. This way of doing doesn't seems very efficient.

I hope I've been sufficiently clear. Sorry if this a well-known pattern, but I spend hours in Nifi doc and trying different data flow without luck.
Any idea?

Philippe Peeters

[http://www.etnic.be/fileadmin/BandeauEtnic.png]

ETNIC   [http://www.etnic.be/fileadmin/LogoEtnic.png]
LE partenaire informatique du progrès de la Fédération Wallonie-Bruxelles

Bâtiment Le Zénith - Boulevard du Roi Albert II, 37 - 1030 Bruxelles

http://www.etnic.be


Disclaimer ETNIC<http://www.etnic.be/index.php?id=537>