title: Comparing JSON arrays in Postgres
body: <p>I have a table representing the items in a cart at different moments in time. The cart items are stored in JSON format, one column with the <code>before</code> values and another with the <code>after</code> values.</p>
  
  <p>My tasks is compare the carts and identify: items added, items removed and price/quantity changes.</p>
  
  <p>I know I can use <code>json_array_elements</code> and a cross join to unpack the JSON blobs, creating a table of items from the JSON values. But I can't think of an efficient way to compare cart items. Can I do this in SQL?</p>
  
  <p>Here is a small example <a href="http://sqlfiddle.com/#!15/8fcdd/6" rel="nofollow noreferrer">SQL Fiddle</a>:</p>
  
  <pre><code>CREATE TABLE t (
    id     INT,
    before JSON,
    after  JSON
  );
  
  INSERT INTO t VALUES (
    1,
    '[{"category":"item","id":"1","price":8,"quantity":1},{"category":"item","id":"2","price":20,"quantity":1},{"category":"item","id":"3","price":3,"quantity":1}]',
    '[{"category":"item","id":"2","price":40,"quantity":1},{"category":"item","id":"3","price":3,"quantity":1},{"category":"item","id":"4","price":2,"quantity":1}]'
  );
  </code></pre>
  
  <p>This expensive query does not solve my problem:</p>
  
  <pre><code>select
    id,
    b.value-&gt;&gt;'id' as id_before,
    b.value-&gt;&gt;'category' as category_before,
    b.value-&gt;&gt;'price' as price_before,
    b.value-&gt;&gt;'quantity' as quantity_before,
    a.value-&gt;&gt;'id' as id_after,
    a.value-&gt;&gt;'category' as category_after,
    a.value-&gt;&gt;'price' as price_after,
    a.value-&gt;&gt;'quantity' as quantity_after
  from t
  CROSS JOIN json_array_elements(before) b
  CROSS JOIN json_array_elements(after) a;
  </code></pre>