Problem in JSON result with QueryCassandra processor

[#1]

Hello All, While working with the QueryCassandra processor, the resultant JSON does not look fine for user defined types. Is there any workaround for this? For the below

CREATE TYPE first.fullname (
  firstname text,
  lastname text
);

CREATE TABLE first.users (
  id uuid PRIMARY KEY,
  name frozen <fullname>,
  direct_reports set<frozen <fullname>>  
); 

INSERT INTO first.users JSON '{
    "id" : "62c36092-82a1-3a00-93d1-46196ee77204",
    "name": {
        "firstname": "person4",
        "lastname": "4"
    },
    "direct_reports": [
        {
            "firstname": "person1",
            "lastname": "1"
        },
        {
            "firstname": "person2",
            "lastname": "2"
        },
        {
            "firstname": "person3",
            "lastname": "3"
        }
    ] 
}';

The resultant JSON has all the User defined types converted to a single string as in the attached screen capture.

Result for an Evaluate JSON Path expression results[0].direct_reports[0].firstname was directed to unmatched since the JSON result was not matching the expected JSON format.

[#2 · Accepted solution]

QueryCassandra does not support user-defined types, and instead will convert the values to strings. As a workaround, you can use ExecuteScript to parse the strings into values. Here is an example Groovy script to accomplish this:

import groovy.json.*

def flowFile = session.get()
if(!flowFile) return
def directReport = flowFile.getAttribute('direct_report')
def json = new JsonSlurper().setType(JsonParserType.LAX).parseText(directReport)
json*.key.each { key -> 
  flowFile = session.putAttribute(flowFile, key,  json[key])
}
session.provenanceReporter.modifyAttributes(flowFile)
session.transfer(flowFile, REL_SUCCESS)

This script assumes you have used something like EvaluateJsonPath to extract $.results[0].directReports[0] into an attribute named 'direct_report'. It parses the JSON object and adds attributes to the flow file for each key/value pair in the object. You can adjust this to work with content rather than attributes, e.g. I have examples of various scripts on my blog.

[#3]

Thanks @Matt Burgess. Currently I am handling this using a Javascript, similar approach to what you described. I wanted to confirm there is no other way. For simpler structures, I managed to extract the key values using Regex, but for deep nested keys, I was forced to use ExecuteScript.