title: How to select many rows from 1 table and insert into a specific JSONB field of a specific row in another table? But in a single raw SQL query
body: <p>postgres 10.3</p>
  
  <p>I have about 1000 rows inside a table called <code>sites</code></p>
  
  <p>If I query like this</p>
  
  <pre><code>SELECT id, name from sites;
  </code></pre>
  
  <p>I will get the 1000 rows.</p>
  
  <p>I also have another table called <code>jsonindexdocument</code> with a single row where the id is 1 and a field called <code>index</code> that is JSONB</p>
  
  <p>Is it possible that in a single query I take out all the 1000 rows in sites table and then update the field called <code>index</code> under id 1?</p>
  
  <p>The format of the json would be</p>
  
  <pre><code>[
    {
       "id": 10,
       "name": "somename"
    },
    {
       "id": 11,
       "name": "another name"
    } // and the rest of the 1000 rows
  ]
  </code></pre>
  
  <p>I am also okay if it uses more than 1 raw SQL statement.</p>
  
  <h2>UPDATE</h2>
  
  <p>I want to add that if the result is empty set, then default to empty array in the json field</p>