Support Questions
Find answers, ask questions, and share your expertise

Query JSON ARRAY using QueryRecord

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

1 REPLY 1

Re: Query JSON ARRAY using QueryRecord

New Contributor

Did you solve it? If so: How?

Thanks!