title: How to use Nifi QueryRecord to lookup for values in a flowfile?
body: <p>I have a scenario where a flowfile contains the json transformed, I need to now combine fields that are in the json, for example, I want to get the R and PA from the Original.ScoreInfo.Team per ID and get Enriched.Team.Source.HV and Enriched.Team.SourceMapped.HV based on the Team.ID. My desired json looks like:</p>
  <pre><code>[{
       TeamID: 1000,
       ValueR: 3, 
       ValuePA: 13},
  {
       TeamID: 2000,
       ValueR: 1, 
       ValuePA: 14}
  ]
  </code></pre>
  <p>Flowfile example:</p>
  <pre><code>    [
    {
      &quot;Mapping&quot;: {
        &quot;ScoreInfo&quot;: {
          &quot;Team&quot;: [
            {
              &quot;ID&quot;: 1,
              &quot;Source&quot;: {
                &quot;HV&quot;: 1,
                &quot;ID&quot;: 1
              }
            },
            {
              &quot;ID&quot;: 3,
              &quot;Source&quot;: {
                &quot;HV&quot;: 2,
                &quot;ID&quot;: 3
              }
            }
          ]
        }
      },
      &quot;Original&quot;: {
        &quot;ScoreInfo&quot;: {
          &quot;Team&quot;: [
            {
              &quot;HV&quot;: 1,
              &quot;ID&quot;: 1,
              &quot;R&quot;: 1,
              &quot;PA&quot;: 8,
              &quot;H&quot;: 2,
              &quot;BB&quot;: 2,
              &quot;SB&quot;: 0,
              &quot;E&quot;: 0,
              &quot;Score&quot;: [
                {
                  &quot;Inn&quot;: 1,
                  &quot;TB&quot;: 2,
                  &quot;R&quot;: 0,
                  &quot;H&quot;: 0,
                  &quot;BB&quot;: 1
                },
                {
                  &quot;Inn&quot;: 2,
                  &quot;TB&quot;: 2,
                  &quot;R&quot;: 1,
                  &quot;H&quot;: 2,
                  &quot;BB&quot;: 1
                }
              ]
            },
            {
              &quot;HV&quot;: 2,
              &quot;ID&quot;: 3,
              &quot;R&quot;: 1,
              &quot;PA&quot;: 10,
              &quot;H&quot;: 3,
              &quot;BB&quot;: 0,
              &quot;SB&quot;: 0,
              &quot;E&quot;: 0,
              &quot;Score&quot;: [
                {
                  &quot;Inn&quot;: 1,
                  &quot;TB&quot;: 1,
                  &quot;R&quot;: 1,
                  &quot;H&quot;: 2,
                  &quot;BB&quot;: 0
                },
                {
                  &quot;Inn&quot;: 2,
                  &quot;TB&quot;: 1,
                  &quot;R&quot;: 0,
                  &quot;H&quot;: 0,
                  &quot;BB&quot;: 0
                }
              ]
            }
          ]
        }
      },
      &quot;MatchValue&quot;: &quot;99999999&quot;,
      &quot;Enriched&quot;: {
        &quot;Team&quot;: [
          {
            &quot;ID&quot;: 1,
            &quot;Source&quot;: {
              &quot;HV&quot;: 1,
              &quot;ID&quot;: 1
            },
            &quot;SourceMapped&quot;: {
              &quot;HV&quot;: 1000,
              &quot;ID&quot;: 1000
            }
          },
          {
            &quot;ID&quot;: 2,
            &quot;Source&quot;: {
              &quot;HV&quot;: 2,
              &quot;ID&quot;: 2
            },
            &quot;SourceMapped&quot;: {
              &quot;HV&quot;: 2000,
              &quot;ID&quot;: 2000
            }
          }
        ],
        &quot;MatchValue&quot;: &quot;99999999&quot;
      }
    }
  ]
  </code></pre>
  <p>I was trying to do it using the <a href="http://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.14.0/org.apache.nifi.processors.standard.QueryRecord/index.html" rel="nofollow noreferrer">QueryRecord</a> and combined with the RPATH but it is returning empty when try this <code>SELECT * FROM FLOWFILE WHERE RPATH(Original, '/ScoreInfo/Team/ID') = RPATH(Enriched, '/Team/ID')</code></p>
  <p>Any idea how can I use the QueryRecord to do it?</p>