title: How to convert arbitrary nested JSON to CSV with jq – so you can convert it back?
body: <p>How do I use <a href="https://stedolan.github.io/jq/" rel="nofollow noreferrer">jq</a> to convert an <strong>arbitrary</strong> JSON array of objects to CSV, while objects in this array are <strong>nested</strong>?</p>
  <p>StackOverflow has a sea of questions/answers where <em>specific</em> input or output fields are referenced, but I'd like to have a generic solution that</p>
  <ol>
  <li>includes a header row,</li>
  <li>works for any JSON input including nested arrays + objects,</li>
  <li>allows records that have missing values for keys that are present in other records</li>
  <li>does not hard-code any field names,</li>
  <li>allows converting the CSV back into the nested JSON structure if needed, and</li>
  <li>uses key paths as header names (see the following description).</li>
  </ol>
  <h2>Dot notation</h2>
  <p>Many JSON-using products (like <a href="https://docs.couchdb.org/en/stable/api/database/find.html#subfields" rel="nofollow noreferrer">CouchDB</a>, <a href="https://docs.mongodb.com/manual/core/document/#dot-notation" rel="nofollow noreferrer">MongoDB</a>, …) and libraries (like <a href="https://docs-lodash.com/v4/get/" rel="nofollow noreferrer">Lodash</a>, …) use variations of syntax that allows access to nested property values / subfields by joining key fragments with a character, often a dot (‘dot notation’).</p>
  <p>An example of a key path like this would be <code>&quot;a.b.0.c&quot;</code> to refer to the deeply nested property in this JSON snippet:</p>
  <pre class="lang-json prettyprint-override"><code>{
    &quot;a&quot;: {
      &quot;b&quot;: [
        {
          &quot;c&quot;: 123,
        }
      ]
    }
  }
  </code></pre>
  <p><strong>Caveat</strong>: Using this method is a pragmatic solution for most cases, but means that either dot characters have to be banned in property names, or a more complex (and definitely never used property name) has to be invented for escaping dots in property names / accessing nested fields. MongoDB simply <a href="https://docs.mongodb.com/manual/core/dot-dollar-considerations/#std-label-crud-concepts-dot-dollar-considerations" rel="nofollow noreferrer">banned usage of <code>&quot;.&quot;</code> in documents until v5.0</a>, some libraries have workarounds for field access (<a href="https://github.com/lodash/lodash/issues/1637" rel="nofollow noreferrer">Lodash example</a>).</p>
  <p><strong>Despite this</strong>, for simplicity, a solution should use the described dot syntax in the CSV output’s header for nested properties. Bonus if there is a solution variant that solves this problem, e.g. with <a href="https://ietf-wg-jsonpath.github.io/draft-ietf-jsonpath-base/draft-ietf-jsonpath-base.html" rel="nofollow noreferrer">JSONPath</a>.</p>
  <h2>Example JSON array as input</h2>
  <pre class="lang-json prettyprint-override"><code>[
      {
          &quot;a&quot;: {
              &quot;b&quot;: [
                  {
                      &quot;c&quot;: 123
                  }
              ]
          }
      },
      {
          &quot;a&quot;: {
              &quot;b&quot;: [
                  {
                      &quot;c&quot;: &quot;foo \&quot; bar&quot;,
                      &quot;d&quot;: &quot;qux&quot;
                  }
              ]
          }
      },
      {
          &quot;a&quot;: {
              &quot;b&quot;: [
                  {
                      &quot;d&quot;: 456
                  }
              ]
          }
      }
  ]
  </code></pre>
  <h2>Example CSV output</h2>
  <p>The output should have a header that includes all fields (even if the object at the first array does not have defined values for all existing key paths).</p>
  <p>To make the output intuitively editable by humans, each row should represent one object in the input array.</p>
  <p>The expected output should look like this:</p>
  <pre><code>&quot;a.b.0.c&quot;,&quot;a.b.0.d&quot;
  123,
  &quot;foo &quot;&quot; bar&quot;,&quot;qux&quot;
  ,456
  </code></pre>
  <h2>Command line</h2>
  <p>This is what I need:</p>
  <pre><code>cat example.json | jq &lt;MISSING CODE HERE&gt;
  </code></pre>