Apache Nifi 如何使用嵌套数组 JSON 加载 JSON 并调用 Oracle 存储过程
我是 Nifi 的新手，但我有一些 JSON 数据，它是一个数据数组，在每个数组元素中，还有另一个嵌套的数组数据.
I am new to Nifi, but I have some JSON data which is an array of data and in each array element, there is another nested array data.
虽然我能够获取文件以加载 JSON，但我遇到的问题如下:
Whilst I am able to get file to load the JSON, where I am struggling is on the following:
- 展平每个数组和嵌套数组 - 包含换行符的嵌套数组存在问题.我已尝试使用处理器将其删除，但未将其删除
- 提取需要的数据
- 要组织提取的数据，将其作为参数传递给 Oracle 存储过程以供执行
我已经使用了 Get File、JoltTransformJson、ExecuteSQL、GetAttributes，但我没有看到一个结构，其中对于 JSON 数组中的每个元素和展平嵌套 JSON 的属性列表可以传递给存储过程.
I have used Get File, JoltTransformJson, ExecuteSQL, GetAttributes, but what I am not seeing is a structure where for each element in the array of JSON and for the flatten out nested JSON a list of attributes that can passed on to the Stored Procedure.
谁能提供任何分步指导?
Can anyone give any step by step guidance?
下面是一封关于 JSON 结构的电子邮件，以提供一个想法-
Below is an email of structure of the JSON to give an idea-
[{app_id":1"，日期时间":2020-11-30T12:21:25.447430"，供应商名称":abc"，doc_id":123345"，数据":"{\n header {\n l_Id = { 1 2 3 3 4 5 6 11 }\n identifer = 123456789\n country = GB\n commonIdentifier {\n ident {\n opps = 6175\n etni {\n format =1234567\n }\n }\n commonIdentityId = 0\n }\n sequence = 0\n timeStamp = 20201130002255.0Z\n timeStampQual = timeFound\n }\n data {\n sequence [0] {\n type = type\n content {\n etni {\n params {\n iD = { 1234567 }\n version = version\n identifier = 1234567\n identifier {\n commonidentityid = 30\n id {\n opps = 6175\n netId{\n 格式 = 12563412\n }\n }\n }\n 时间戳 {\n localTime {\ngeneraltime = 20201130122255.000Z\n BSWT = notProvided\n }\n }\n direction = forward\n state = nothing\n release = 9\n nature = ball\n }\n }\n }\n }\n }\n}\n",},{app_id":1"，日期时间":2020-11-30T12:21:25.447430"，供应商名称":abc"，doc_id":123345"，数据":"{\n header {\n l_Id = { 1 2 3 3 4 5 6 11 }\n identifer = 123456789\n country = GB\n commonIdentifier {\n ident {\n opps = 6175\n etni {\n format =1234567\n }\n }\n commonIdentityId = 0\n }\n sequence = 0\n timeStamp = 20201130002255.0Z\n timeStampQual = timeFound\n }\n data {\n sequence [0] {\n type = type\n content {\n etni {\n params {\n iD = { 1234567 }\n version = version\n identifier = 1234567\n identifier {\n commonidentityid = 30\n id {\n opps = 6175\n netId{\n 格式 = 12563412\n }\n }\n }\n 时间戳 {\n localTime {\ngeneraltime = 20201130122255.000Z\n BSWT = notProvided\n }\n }\n direction = forward\n state = nothing\n release = 9\n nature = ball\n }\n }\n }\n }\n }\n}\n",}]
[ { "app_id":"1", "date_time":"2020-11-30T12:21:25.447430", "supplier_name":"abc", "doc_id":"123345", "data":" {\n header {\n l_Id = { 1 2 3 3 4 5 6 11 }\n identifer = 123456789\n country = GB\n commonIdentifier {\n ident {\n opps = 6175\n etni {\n format = 1234567\n }\n }\n commonIdentityId = 0\n }\n sequence = 0\n timeStamp = 20201130002255.0Z\n timeStampQual = timeFound\n }\n data {\n sequence [0] {\n type = type\n contents {\n etni {\n params {\n iD = { 1234567 }\n version = version\n identifier = 1234567\n identifier {\n commonidentityid = 30\n id {\n opps = 6175\n netId {\n format = 12563412\n }\n }\n }\n timeStamp {\n localTime {\n generaltime = 20201130122255.000Z\n BSWT = notProvided\n }\n }\n direction = forward\n state = nothing\n release = 9\n nature = ball\n }\n }\n }\n }\n }\n}\n", }, { "app_id":"1", "date_time":"2020-11-30T12:21:25.447430", "supplier_name":"abc", "doc_id":"123345", "data":" {\n header {\n l_Id = { 1 2 3 3 4 5 6 11 }\n identifer = 123456789\n country = GB\n commonIdentifier {\n ident {\n opps = 6175\n etni {\n format = 1234567\n }\n }\n commonIdentityId = 0\n }\n sequence = 0\n timeStamp = 20201130002255.0Z\n timeStampQual = timeFound\n }\n data {\n sequence [0] {\n type = type\n contents {\n etni {\n params {\n iD = { 1234567 }\n version = version\n identifier = 1234567\n identifier {\n commonidentityid = 30\n id {\n opps = 6175\n netId {\n format = 12563412\n }\n }\n }\n timeStamp {\n localTime {\n generaltime = 20201130122255.000Z\n BSWT = notProvided\n }\n }\n direction = forward\n state = nothing\n release = 9\n nature = ball\n }\n }\n }\n }\n }\n}\n", } ]
推荐答案
有FlattenJSON 处理器如果您想拆分 JSON，则可以使用 JOLT 和一些网站，例如 JOLT 变换
there is the FlattenJSON Processor If you're looking to split the JSON, there is the possibility to use JOLT and some sites such as this JOLT transformation
另一种选择是相应地拆分 JSON 并使用拆分值.我发现网站 https://jsonpath.com/ 有助于决定拆分的工作方式.我知道这不是一步一步的，但是使用 SplitJSON 处理器，然后可能是 ExtractText 处理器，您将能够获得所需的信息.如果不知道数据的确切格式，以及需要哪些数据部分，就很难一步一步地提供准确的数据.
Another option could be to split the JSON accordingly and use the split values. I have found that the website https://jsonpath.com/ is helpful for deciding how the split could work. I know this isnt a step by step, but using the SplitJSON processor and then maybe an ExtractText Processor, you would be able to get the desired information. Without knowing the exact format of the data, and which parts of the data that would be desired, providing an exact step by step will be difficult.
相关文章