title: How to automatically process sqlite export to get JSON that reflect relations DB?
body: <p>I'm exporting data from a <a href="https://github.com/Labx/LibraryBox-app/blob/7c612c65d60846d801154a8eefcfb18e10c3a833/app/data/data.sqlite3?raw=true" rel="nofollow">sqlite database</a> (no choice here) to generate a JSON for an AngulaJS app.</p>
  
  <h2>JSON export from SQLite</h2>
  
  <p><code>sqlite3</code> CSV export <strong>flatten the relational structure and add duplication</strong>:</p>
  
  <pre class="lang-json prettyprint-override"><code>[
    {
      "bid": 5, // book id
      "aid": 4,  // author's id
      "tags": 3,
      "title": "Jacques le fataliste et son maître",
    },
    {
      "bid": 5,
      "aid": 23, // same book another author
      "tags": 8, // same book another tag
      "title": "Jacques le fataliste et son maître",
    }
    …
  ]
  </code></pre>
  
  <h3>SQLite to JSON command</h3>
  
  <p>The <a href="https://github.com/Labx/LibraryBox-app/blob/7c612c65d60846d801154a8eefcfb18e10c3a833/app/data/data.sqlite3?raw=true" rel="nofollow">sqlite database</a> is available on the github and the command I use to export/convert is:</p>
  
  <pre class="lang-shell prettyprint-override"><code>sqlite3 -csv -header app/data/data.sqlite3 \
  "SELECT b.id as bid, title, b.sort as sort_book, a.id as aid, a.sort as sort_author, path, name FROM books as b inner join books_authors_link as b_a ON b.id = b_a.book INNER JOIN authors as a ON a.id = b_a.author" \
  | ./node_modules/csvtojson/bin/csvtojson \
  &gt; authors-books.json
  </code></pre>
  
  <h2>JSON Goal</h2>
  
  <p>I designed the final JSON with my customer in a document-oriented approach:</p>
  
  <ul>
  <li>all I need in an object ;</li>
  <li>references to other resources as
  <ul>
  <li>an <em>id</em>/<em>primary key</em> for forein key ;</li>
  <li>a list of _id_s/<em>primary keys</em> for many-to-many relations.</li>
  </ul></li>
  </ul>
  
  <p>Here is an example: of <strong>my goal</strong>:</p>
  
  <pre class="lang-json prettyprint-override"><code>[
      {
          "id": 2,
          "authors": [4, 23],
          "tags": [3,8,29,69],
          "title": "Jacques le fataliste et son maître",
          …
      }
      …
  ]
  </code></pre>
  
  <p>If you speak French, here is the <a href="https://github.com/Labx/LibraryBox-app/issues/8" rel="nofollow">github issue from the project</a></p>
  
  <h2>Question</h2>
  
  <p>So how can I get this result using a command line pipe or javascript-based tool?</p>