title: Flatten nested JSON structure in PostgreSQL
body: <p>I'm trying to write a Postgres query that will output my <code>json</code> data in a particular format. </p>
  
  <p><strong>JSON data structure</strong></p>
  
  <pre><code>{
      user_id: 123,
      data: {
          skills: {
              "skill_1": {
                  "title": "skill_1",
                  "rating": 4,
                  "description": 'description text'
              },
              "skill_2": {
                  "title": "skill_2",
                  "rating": 2,
                  "description": 'description text'
              },
              "skill_3": {
                  "title": "skill_3",
                  "rating": 5,
                  "description": 'description text'
              },
              ...
          }
      }
  }
  </code></pre>
  
  <p>This is how I need the data to be formatted in the end:</p>
  
  <pre><code>[
      {
          user_id: 123,
          skill_1: 4, 
          skill_2: 2, 
          skill_3: 5, 
                      ... 
      },
      {
          user_id: 456,
          skill_1: 1, 
          skill_2: 3, 
          skill_3: 4, 
                      ... 
      }
  ]
  </code></pre>
  
  <p>So far I'm working with a query that looks like this:</p>
  
  <pre><code>SELECT
      user_id,
      data#&gt;&gt;'{skills, "skill_1",  rating}' AS "skill_1",
      data#&gt;&gt;'{skills, "skill_2",  rating}' AS "skill_2",
      data#&gt;&gt;'{skills, "skill_3",  rating}' AS "skill_3"
  FROM some_table
  </code></pre>
  
  <p>There has to be a better way to go about writing my query. There are 400+ rows and 70+ skills. My above query is a little crazy. Any guidance or help would be greatly appreciated.</p>
  
  <p><strong>Some things to note:</strong></p>
  
  <ol>
  <li>Users rated themselves on 70+ skills</li>
  <li>Each skill object has the same structure</li>
  <li>Each user rated themselves on the exact same set of skills</li>
  </ol>