title: Convert a CSV into a JSON using a JSON Schema
body: <p><strong>How do I convert a flat table into a JSON?</strong></p>
  <p>I have previously converted JSONs into Flat Tables using both custom code and libraries. However, what I am aiming to do here is the reverse. Before going ahead and creating a custom library, I was wondering if anyone had encountered this problem before and if there was an existing solution to it.</p>
  <p>When you flatten a JSON into a CSV, you loose the information on the structure, and therefore to reverse it, you need a document that describes how the JSON should be built, which ideally would be the standardised <a href="https://json-schema.org/" rel="nofollow noreferrer">JSON Schema</a>.</p>
  <p>The following example shows a source CSV, the JSON Schema and the expected output.</p>
  <p><strong>User CSV</strong></p>
  <pre><code>user_id, adress.city, address.street, address.number, name, aka, contacts.name, contacts.relationship
  1, Seattle, Atomic Street, 6910, Rick Sanchez, Rick, Morty, Grandson
  1, Seattle, Atomic Street, 6910, Rick Sanchez, Grandpa, Morty, Grandson
  1, Seattle, Atomic Street, 6910, Rick Sanchez, Albert Ein-douche, Morty, Grandson
  1, Seattle, Atomic Street, 6910, Rick Sanchez, Richard, Morty, Grandson
  1, Seattle, Atomic Street, 6910, Rick Sanchez, Rick, Beth, Daughter
  1, Seattle, Atomic Street, 6910, Rick Sanchez, Grandpa, Beth, Daughter
  1, Seattle, Atomic Street, 6910, Rick Sanchez, Albert Ein-douche, Beth, Daughter
  1, Seattle, Atomic Street, 6910, Rick Sanchez, Richard, Beth, Daughter
  </code></pre>
  <p><strong>JSON Schema</strong></p>
  <p>This follows the defined standard with the addition of the &quot;source&quot; property. I am suggesting adding this custom property to this specific problem in order to map between the csv columns and the JSON values (leafs).</p>
  <pre class="lang-json prettyprint-override"><code>{
   &quot;$schema&quot;: &quot;https://json-schema.org/draft/2020-12/schema&quot;,
   &quot;title&quot;: &quot;User&quot;,
   &quot;type&quot;: &quot;object&quot;,
   &quot;properties&quot;:{
    &quot;user_id&quot; : {&quot;type&quot;:&quot;integer&quot;, &quot;source&quot;:&quot;user_id&quot;},
    &quot;address&quot;:{
     &quot;type&quot;:&quot;object&quot;,
     &quot;properties&quot;:{
      &quot;city&quot; : {&quot;type&quot;:&quot;string&quot;, &quot;source&quot;:&quot;adress.city&quot;},
      &quot;street&quot; : {&quot;type&quot;:&quot;string&quot;, &quot;source&quot;:&quot;adress.street&quot;},
      &quot;number&quot;: { &quot;type&quot;:&quot;integer&quot;, &quot;source&quot;:&quot;adress.number&quot;}
     }
    },
    &quot;name&quot; : {&quot;type&quot;:&quot;string&quot;, &quot;source&quot;:&quot;name&quot;}},
    &quot;aka&quot;:{
     &quot;type&quot;: &quot;array&quot;,
     &quot;items&quot; : {&quot;type&quot;:&quot;string&quot;, &quot;source&quot;:&quot;aka&quot;}
    },
    &quot;contacts&quot;:{
     &quot;type&quot;:&quot;array&quot;,
     &quot;items&quot;:{
      &quot;type&quot;:&quot;object&quot;,
      &quot;properties&quot;:{
       &quot;name&quot; : {&quot;type&quot;:&quot;string&quot;, &quot;source&quot;:&quot;contacts.name&quot;},
       &quot;relationship&quot;:{&quot;type&quot;:&quot;string&quot;, &quot;source&quot;:&quot;contacts.relationship&quot;}
      },
     }
    }
   }
  }
  </code></pre>
  <p><strong>Expected JSON</strong></p>
  <pre class="lang-json prettyprint-override"><code>{
   &quot;user_id&quot;:1,
   &quot;address&quot;:{
    &quot;city&quot;:&quot;Seattle&quot;,
    &quot;street&quot;:&quot;Atomic Street&quot;,
    &quot;number&quot;:6910
   },
   &quot;name&quot;:&quot;Rick Sanchez&quot;,
   &quot;aka&quot;:[
    &quot;Rick&quot;,
    &quot;Grandpa&quot;,
    &quot;Albert Ein-douche&quot;,
    &quot;Richard&quot;
   ],
   &quot;contacts&quot;:[
    {
     &quot;name&quot;:&quot;Morty&quot;,
     &quot;relationship&quot;:&quot;Grandson&quot;
    },
    {
     &quot;name&quot;:&quot;Beth&quot;,
     &quot;relationship&quot;:&quot;Daughter&quot;
    }
   ]
  }
  </code></pre>
  <p>From the above we see that although there are 8 rows in the CSV, we are producing a single JSON Object (instead of 8) since there is only one unique user (user_id = 1). This could be inferred from the JSON Schema where the root element is an object and not a list.</p>
  <p>If we did not specify a JSON Schema or other kind of mapping, you could simply assume no structure and just create 8 flat jsons as below</p>
  <pre class="lang-json prettyprint-override"><code>[
   {&quot;user_id&quot;:1,&quot;address.city&quot;:&quot;Seattle&quot;, ... &quot;aka&quot;:&quot;Rick&quot; ... &quot;contacts.relationship&quot;:&quot;Grandson&quot;}
   ...
   {&quot;user_id&quot;:1,&quot;address.city&quot;:&quot;Seattle&quot;, ... &quot;aka&quot;:&quot;Richard&quot; ... &quot;contacts.relationship&quot;:&quot;Daughter&quot;}
  ]
  </code></pre>
  <p>I am adding the Python tag since that is the language I use mostly, but in this case, the solution doesn't need to be in Python.</p>