Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Query JSON ARRAY using QueryRecord

Query JSON ARRAY using QueryRecord

New Contributor

Hi,


I have the following JSON Record in my incoming Flowfile to QueryRecord Processor and wanted specific fields from the content. I want Index, Sentiment and max(SentimentScore)


{
    "ResultList": [
        {
            "Index": 0, 
            "Sentiment": "NEUTRAL", 
            "SentimentScore": {
                "Mixed": 0.014603961259126663, 
                "Positive": 0.2183697521686554, 
                "Neutral": 0.7095515727996826, 
                "Negative": 0.05747472867369652
            }
        }, 
        {
            "Index": 1, 
            "Sentiment": "NEGATIVE", 
            "SentimentScore": {
                "Mixed": 0.20378299057483673, 
                "Positive": 0.11562751978635788, 
                "Neutral": 0.10683445632457733, 
                "Negative": 0.5737550258636475
            }
        }, 
        {
            "Index": 2, 
            "Sentiment": "NEGATIVE", 
            "SentimentScore": {
                "Mixed": 0.007578125223517418, 
                "Positive": 0.004836051259189844, 
                "Neutral": 0.01562119647860527, 
                "Negative": 0.9719645380973816
            }
        }, 
        {
            "Index": 3, 
            "Sentiment": "POSITIVE", 
            "SentimentScore": {
                "Mixed": 0.008649133145809174, 
                "Positive": 0.8763676881790161, 
                "Neutral": 0.11021242290735245, 
                "Negative": 0.004770802799612284
            }
        }, 
        {
            "Index": 4, 
            "Sentiment": "NEUTRAL", 
            "SentimentScore": {
                "Mixed": 0.0017022271640598774, 
                "Positive": 0.03283606469631195, 
                "Neutral": 0.9602330327033997, 
                "Negative": 0.005228717811405659
            }
        }, 
        {
            "Index": 5, 
            "Sentiment": "NEGATIVE", 
            "SentimentScore": {
                "Mixed": 0.02237289398908615, 
                "Positive": 0.2910439670085907, 
                "Neutral": 0.3056293725967407, 
                "Negative": 0.38095375895500183
            }
        }, 
        {
            "Index": 6, 
            "Sentiment": "POSITIVE", 
            "SentimentScore": {
                "Mixed": 0.00902993232011795, 
                "Positive": 0.976735532283783, 
                "Neutral": 0.011653305031359196, 
                "Negative": 0.002581255976110697
            }
        }, 
        {
            "Index": 7, 
            "Sentiment": "NEGATIVE", 
            "SentimentScore": {
                "Mixed": 0.011669524013996124, 
                "Positive": 0.0006034726393409073, 
                "Neutral": 0.17051522433757782, 
                "Negative": 0.8172117471694946
            }
        }, 
        {
            "Index": 8, 
            "Sentiment": "NEGATIVE", 
            "SentimentScore": {
                "Mixed": 0.010518478229641914, 
                "Positive": 0.002629985800012946, 
                "Neutral": 0.01846659556031227, 
                "Negative": 0.9683849811553955
            }
        }, 
        {
            "Index": 9, 
            "Sentiment": "NEUTRAL", 
            "SentimentScore": {
                "Mixed": 0.009224030189216137, 
                "Positive": 0.04947839304804802, 
                "Neutral": 0.7379242181777954, 
                "Negative": 0.20337335765361786
            }
        }
    ], 
    "ErrorList": []
}

Am using the Following Schema in the JsonTreeReader,

{
  "type" : "record",
  "name" : "SentimentOutput",
  "fields" : [ {
    "name" : "ResultList",
    "type" : {
      "type" : "array",
      "items" : {
        "type" : "record",
        "name" : "ResultList",
        "fields" : [ {
          "name" : "Index",
          "type" : "int",
          "doc" : "Type inferred from '0'"
        }, {
          "name" : "Sentiment",
          "type" : "string",
          "doc" : "Type inferred from '\"NEUTRAL\"'"
        }, {
          "name" : "SentimentScore",
          "type" : {
            "type" : "record",
            "name" : "ResultList",
            "namespace" : "SentimentScore",
            "fields" : [ {
              "name" : "Mixed",
              "type" : "double",
              "doc" : "Type inferred from '0.014603961259126663'"
            }, {
              "name" : "Positive",
              "type" : "double",
              "doc" : "Type inferred from '0.2183697521686554'"
            }, {
              "name" : "Neutral",
              "type" : "double",
              "doc" : "Type inferred from '0.7095515727996826'"
            }, {
              "name" : "Negative",
              "type" : "double",
              "doc" : "Type inferred from '0.05747472867369652'"
            } ]
          },
          "doc" : "Type inferred from '{\"Mixed\":0.014603961259126663,\"Positive\":0.2183697521686554,\"Neutral\":0.7095515727996826,\"Negative\":0.05747472867369652}'"
        } ]
      }
    },
    "doc" : "Type inferred from '[{\"Index\":0,\"Sentiment\":\"NEUTRAL\",\"SentimentScore\":{\"Mixed\":0.014603961259126663,\"Positive\":0.2183697521686554,\"Neutral\":0.7095515727996826,\"Negative\":0.05747472867369652}},{\"Index\":1,\"Sentiment\":\"NEGATIVE\",\"SentimentScore\":{\"Mixed\":0.20378299057483673,\"Positive\":0.11562751978635788,\"Neutral\":0.10683445632457733,\"Negative\":0.5737550258636475}},{\"Index\":2,\"Sentiment\":\"NEGATIVE\",\"SentimentScore\":{\"Mixed\":0.007578125223517418,\"Positive\":0.004836051259189844,\"Neutral\":0.01562119647860527,\"Negative\":0.9719645380973816}},{\"Index\":3,\"Sentiment\":\"POSITIVE\",\"SentimentScore\":{\"Mixed\":0.008649133145809174,\"Positive\":0.8763676881790161,\"Neutral\":0.11021242290735245,\"Negative\":0.004770802799612284}},{\"Index\":4,\"Sentiment\":\"NEUTRAL\",\"SentimentScore\":{\"Mixed\":0.0017022271640598774,\"Positive\":0.03283606469631195,\"Neutral\":0.9602330327033997,\"Negative\":0.005228717811405659}},{\"Index\":5,\"Sentiment\":\"NEGATIVE\",\"SentimentScore\":{\"Mixed\":0.02237289398908615,\"Positive\":0.2910439670085907,\"Neutral\":0.3056293725967407,\"Negative\":0.38095375895500183}},{\"Index\":6,\"Sentiment\":\"POSITIVE\",\"SentimentScore\":{\"Mixed\":0.00902993232011795,\"Positive\":0.976735532283783,\"Neutral\":0.011653305031359196,\"Negative\":0.002581255976110697}},{\"Index\":7,\"Sentiment\":\"NEGATIVE\",\"SentimentScore\":{\"Mixed\":0.011669524013996124,\"Positive\":6.034726393409073E-4,\"Neutral\":0.17051522433757782,\"Negative\":0.8172117471694946}},{\"Index\":8,\"Sentiment\":\"NEGATIVE\",\"SentimentScore\":{\"Mixed\":0.010518478229641914,\"Positive\":0.002629985800012946,\"Neutral\":0.01846659556031227,\"Negative\":0.9683849811553955}},{\"Index\":9,\"Sentiment\":\"NEUTRAL\",\"SentimentScore\":{\"Mixed\":0.009224030189216137,\"Positive\":0.04947839304804802,\"Neutral\":0.7379242181777954,\"Negative\":0.20337335765361786}}]'"
  }, {
    "name" : "ErrorList",
    "type" : {
      "type" : "array",
      "items" : "null"
    },
    "doc" : "Type inferred from '[]'"
  } ]
}

And the Query i use is;

select (ResultList, '/Index') as "index", (ResultList, '/Sentiment') as "sentiment", (ResultList, max('/SentimentScore')) as "score" from FLOWFILE


While Executing i get the following Error,


109531-1561158765900.png


Can someone help me achieving this use case?

Tagging : @Shu,@Matt Burgess,@Matt Clarke

Don't have an account?
Coming from Hortonworks? Activate your account here