title: PostgreSQL: Efficiently split JSON array into rows
body: <p>I have a table (Table A) that includes a text column that contains JSON encoded data.</p>
  
  <p>The JSON data is always an array with between one and a few thousand plain object.</p>
  
  <p>I have another table (Table B) with a few columns, including a column with a datatype of 'JSON'</p>
  
  <p>I want to select all the rows from table A, split the json array into its elements and insert each element into table B</p>
  
  <p>Bonus objective: Each object (almost) always has a key, <code>x</code>. I want to pull the value of <code>x</code> out into column, and delete <code>x</code> from the original object (if it exists).</p>
  
  <p>E.g.: Table A</p>
  
  <pre><code>| id | json_array (text)              |
  +----+--------------------------------+
  |  1 | '[{"x": 1}, {"y": 8}]'         |
  |  2 | '[{"x": 2, "y": 3}, {"x": 1}]' |
  |  3 | '[{"x": 8, "z": 2}, {"z": 3}]' |
  |  4 | '[{"x": 5, "y": 2, "z": 3}]'   |
  </code></pre>
  
  <p>...would become: Table B</p>
  
  <pre><code>| id | a_id | x    | json (json)        |
  +----+------+------+--------------------+
  |  0 |    1 |    1 | '{}'               |
  |  1 |    1 | NULL | '{"y": 8}'         |
  |  2 |    2 |    2 | '{"y": 3}'         |
  |  3 |    2 |    1 | '{}'               |
  |  4 |    3 |    8 | '{"y": 2}'         |
  |  5 |    3 | NULL | '{"z": 3}'         |
  |  6 |    4 |    5 | '{"y": 2, "z": 3}' |
  </code></pre>
  
  <p>This initially has to work on a few million rows, and would then need to be run at regular intervals, so making it efficient would be a priority.</p>
  
  <p>Is it possible to do this without using a loop and PL/PgSQL? I haven't been making much progress.</p>