title: Optimize JSON denormalization using JQ - "cartesian product" from 1:N
body: <p>I have a JSON database change log, output of <code>wal2json</code>. It looks like this:</p>
  
  <pre><code>{"xid":1190,"timestamp":"2018-07-19 17:18:02.905354+02","change":[
      {"kind":"update","table":"mytable2","columnnames":["id","name","age"],"columnvalues":[401,"Update AA",20],"oldkeys":{"keynames":["id"],"keyvalues":[401]}},
      {"kind":"update","table":"mytable2","columnnames":["id","name","age"],"columnvalues":[401,"Update BB",20],"oldkeys":{"keynames":["id"],"keyvalues":[401]}}]}
   ...
  </code></pre>
  
  <p>Each top level entry (<code>xid</code>) is a transaction, each item in <code>change</code> is, well, a change. One row may change multiple times.</p>
  
  <p>To import to an OLAP system with limited feature set,  I need to have the order explicitly stated. So I need to add a <code>sn</code> for each change in a transaction.<br>
  Also, each change must be a top level entry - the OLAP can't iterate sub-items within one entry.</p>
  
  <pre><code>{"xid":1190, "sn":1, "kind":"update", "data":{"id":401,"name":"Update AA","age":20} }
  {"xid":1190, "sn":2, "kind":"update", "data":{"id":401,"name":"Update BB","age":20} }
  {"xid":1191, "sn":1, "kind":"insert", "data":{"id":625,"name":"Inserted","age":20} }
  {"xid":1191, "sn":2, "kind":"delete", "data":{"id":625} }
  </code></pre>
  
  <p>(The reason is that the OLAP has limited ability to transform the data during import, and also doesn't have the order as a parameter.)</p>
  
  <p>So, I do this using <code>jq</code>:</p>
  
  <pre><code>function transformJsonDataStructure {
      ## First let's reformat it to XML, then transform using XPATH, then back to JSON.
  
      ## Example input:
      # {"xid":1074,"timestamp":"2018-07-18 17:49:54.719475+02","change":[
      #   {"kind":"update","table":"mytable2","columnnames":["id","name","age"],"columnvalues":[401,"Update AA",20],"oldkeys":{"keynames":["id"],"keyvalues":[401]}},
      #   {"kind":"update","table":"mytable2","columnnames":["id","name","age"],"columnvalues":[401,"Update BB",20],"oldkeys":{"keynames":["id"],"keyvalues":[401]}}]}
      cat "$1" | while read -r LINE ; do
          XID=`echo "$LINE" | jq -c '.xid'`;
          export SN=0;
          #serr "{xid: $XID, changes: $CHANGES}";
          echo "$LINE" | jq -c '.change[]' | while read -r CHANGE ; do
              SN=$((SN+=1))
              KIND=`echo "$CHANGE" | jq -c --raw-output .kind`;
              TABLE=`echo "$CHANGE" | jq -c --raw-output .table`;
              DEST_FILE="$TARGET_PATH-$TABLE.json";
              case "$KIND" in
                  update|insert)
                      MAP=$(convertTwoArraysToMap "$(echo "$CHANGE" | jq -c ".columnnames")" "$(echo "$CHANGE" | jq -c ".columnvalues")") ;;
                  delete)
                      MAP=$(convertTwoArraysToMap "$(echo "$CHANGE" | jq -c ".oldkeys.keynames")" "$(echo "$CHANGE" | jq -c ".oldkeys.keyvalues")") ;;
              esac
              #echo "{\"xid\":$XID, \"table\":\"$TABLE\", \"kind\":\"$KIND\", \"data\":$MAP }" &gt;&gt; "$DEST_FILE"; ;;
              echo "{\"xid\":$XID, \"sn\":$SN, \"kind\":\"$KIND\", \"data\":$MAP }" | tee --append "$DEST_FILE";
          done;
      done;
  
      return;
  }
  </code></pre>
  
  <p>The problem is the performance. I am calling <code>jq</code> few times per entry. This is quite slow, around 1000x times slower than without the transformation.</p>
  
  <p><strong>How can perform the transformation above using just one pass?</strong> (<code>jq</code> is not a must, other tool can be used too, but should be in CentOS packages. I want to avoid coding an extra tool for that. </p>
  
  <p>From <code>man jq</code> it seems that it could be capable of processing the whole file (JSON entry per row) in one go. I could do it in XSLT but I can't wrap my head around <code>jq</code>. Especially the iteration of the <code>change</code> array and combining <code>columnnames</code> and <code>columnvalues</code> to a map.  </p>
  
  <ul>
  <li>For the iteration, I think <code>map</code> or <code>map_values</code> could be used.</li>
  <li>For the 2 arrays to map, I see the <code>from_entries</code> and <code>with_entries</code> functions, but can't get it work.</li>
  </ul>
  
  <p>Any <code>jq</code> master around to advise?</p>