title: Proper way to archive large JSON objects in a PostgreSQL table that will be API accessible?
body: <p>I've been working at this problem for a bit now. I'm working on a statistics website as a hobby for a game that I play.</p>
  
  <p>Basically I have a script accessing the game's API every 5 minutes (probably going to increase this to 15 minutes) and pulling the current state of all the matches at once. I was originally storing this object as a JSON column in my table. (Each row then had a 118kb object in the JSON column)</p>
  
  <p>The problem was trying to query the table to get the entire archive for a one week period (which is the duration of the match). Basically, it was pulling 2016 118kb records for a week long match-up when all I wanted was a specific key out of the JSON. Requests to this API endpoint are taking about 10 seconds to complete!</p>
  
  <p>I've only found ways in PostgreSQL to query a row based on a JSON key, but not a way to do something like <code>SELECT match.kills FROM matches WHERE...</code>. </p>
  
  <p>I've realized that that's not going to work so I want to try to take keys from the JSON objects and insert them into the corresponding table column.</p>
  
  <p>The JSON object skeleton looks like this:
  </p>
  
  <pre><code>{
    id: string,
    start_time: timestamp,
    end_time: timestamp,
    scores: {
      green: number,
      blue: number,
      red: number 
    },
    worlds: number[],
    all_worlds: number[][],
    deaths: {
      green: number,
      blue: number,
      red: number
    },
    kills: {
      green: number,
      blue: number,
      red: number
    },
    maps: [
      {
        id: number,
        type: string,
        scores: same as above,
        bonuses: {
          type: string,
          owner: string
        },
        deaths: same as above,
        kills: same as above,
        objectives: [
          {
            id: string,
            type: string,
            owner: string,
            last_flipped: timestamp,
            claimed_by: guild id (put this into another api endpoint),
            claimed_at: timestamp
          },
          ... (repeat 17 times)
        ]
      },
      ... (repeat 3 times)
    ]
  }
  </code></pre>
  
  <p>So I want to store this in my database with the keys as columns, but I'm not quite sure how to accomplish it for keys with values of the type <code>object</code>.</p>
  
  <p>The end goal is to store this in a way that I'll have an API accessible by a URL such as:</p>
  
  <p><code>mywebsite.com/api/v1/matcharchive?data=kills,deaths,score&amp;matchid=1-1&amp;archive_time=2016-07-09T02:00:00Z</code></p>
  
  <p>and it will query the database for only those 3 keys in the object and return them.</p>
  
  <p>What is the proper way to store a JSON object with this many keys into a PSQL table?</p>