Nifi write JSON object with array into MySQL table

Question

I have just begun to use Nifi to read in JSON array and trying to save the output into a MySQL table.

The main issue is that the "extraDetails" is an array and I would need the id to be inserted with each extraDetails element.

This is a sample of the JSON:

[
  {
    "id": "2fa84997-b15f-4859-b315-8125ba79555f",
    "extraDetails": [
      {
        "from": "2023-10-06T03:05",
        "to": "2026-10-07T03:05:44",
        "additionalDetails": "{\"serviceId\":13,\"serviceTierIds\":[73,74],\"serviceEntitlementIds\":[]}"
      },
      {
        "from": "2023-10-06T03:06",
        "to": "2026-10-07T03:06:20",
        "additionalDetails": "{\"serviceId\":14,\"serviceTierIds\":[75,76,77,78],\"serviceEntitlementIds\":[]}"
      },
      {
        "from": "2023-10-06T03:06",
        "to": "2026-10-07T03:06:47",
        "additionalDetails": "{\"serviceId\":3,\"serviceTierIds\":[67,68],\"serviceEntitlementIds\":[]}"
      }
    ]
  },
  {
    "id": "cade90bd-62a3-48ce-87a0-0452e3efb3fc",
    "extraDetails": [
      {
        "from": "2023-10-27T11:00",
        "to": "2026-10-27T11:00:45",
        "additionalDetails": "{\"serviceId\":1,\"serviceTierIds\":[59,58,57,56,55,60,61],\"serviceEntitlementIds\":[]}"
      }
    ]
  }
]

My table columns are: id | from | to | additionalDetails.

I have tried SplitJson but it seems to remove the "id" attribute value.

I am currently reading up on JoltTransformJSON to see if it can split the nested JSON while still providing the "id".

Please let me know if this is the right direction?

lepdrop7 · Accepted Answer · 2025-04-21 10:01:46Z

0

I have just begun to use Nifi to read in JSON array and trying to save the output into a MySQL table.

answered Apr 21 at 10:01

lepdrop7

1

Sign up to request clarification or add additional context in comments.

1 Comment

Community Apr 21 at 17:28

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Eugene · Accepted Answer · 2025-04-22 03:16:09Z

0

In case anyone needs, I found a similar post using Jolt transform here which helps: Split array inside JSON with JOLT

answered Apr 22 at 3:16

Eugene

1,1471 gold badge27 silver badges49 bronze badges

Comments

Arnold Joseph · Accepted Answer · 2025-04-22 16:33:29Z

0

Hi Eugene this spec will help you resolve your query,
I have added explanation of spec along with the spec.

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "id": "[#2].id", // keep the id as it is on first array level #2 for 1st array.
        "extraDetails": {
          "*": {
            "*": "[#4].extraDetails.[#2].&",// keep all elements as it is on first and second levels of array so used #4 and #2
            "@(2,id)": "[#4].extraDetails.[#2].id"// grabbing value 2 levels of id and placing inside extraDetails array
          }
        }
      }
    }
  }
]

edited Apr 22 at 16:33

answered Apr 22 at 16:02

Arnold Joseph

5455 silver badges19 bronze badges

Comments

Barbaros Özhan · Accepted Answer · 2025-04-23 19:23:46Z

The following processors might be used for a complete solution :

Assume that your JSON value is in a file, then add a

GetFile processor which has
- Input Directory is defined such as C:\ApacheNiFi\App\file\json
- Keep Source File is set to true

JoltTransformJSON processor which has

Jolt Transformation DSL is set to Chain
Jolt Specification is

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "extraDetails": {
          "*": {
            "@2,id": "&3_&1.id", //bring "id" value from 2 upper level 
            "*": "&3_&1.&"
          }
        }
      }
    }
  },
  {//remove object keys
    "operation": "shift",
    "spec": {
      "*": "[]"
    }
  }
]

that generates an array of objects with no keys, eg. flattens the JSON making it suitable for a CSV conversion

ConvertRecord processor which converts JSON to CSV with the following features :
- Record Reader is set to JsonTreeReader
- Record Writer is set to CSVRecordSetWriter
PutDatabaseRecord processor which has
- Record Reader is set to CSVReader
- Statement Type is set to INSERT
- Database Connection Pooling Service is set to DBCPConnectionPool , as an example name, which is configured as a Controller Service for JDBC Connection Pool
- Schema Name and Table Name are defined

An example illustration follows :

Collectives™ on Stack Overflow

Nifi write JSON object with array into MySQL table

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related