title: PostgreSQL compare two jsonb objects
body: <p>With <a href="http://www.postgresql.org/" rel="noreferrer">PostgreSQL(v9.5)</a>, the <a href="https://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql">JSONB</a> formats give awesome opportunities. But now I'm stuck with what seems like a relatively simple operation;</p>
  
  <p>compare two jsonb objects; see what is different or missing in one document compared to the other.</p>
  
  <p><strong>What I have so far</strong></p>
  
  <pre><code>WITH reports(id,DATA) AS (
            VALUES (1,'{"a":"aaa", "b":"bbb", "c":"ccc"}'::jsonb),
                   (2,'{"a":"aaa", "b":"jjj", "d":"ddd"}'::jsonb) )
  SELECT jsonb_object_agg(anon_1.key, anon_1.value)
  FROM
    (SELECT anon_2.key AS KEY,
        reports.data -&gt; anon_2.KEY AS value
     FROM reports,
       (SELECT DISTINCT jsonb_object_keys(reports.data) AS KEY
        FROM reports) AS anon_2
     ORDER BY reports.id DESC) AS anon_1
  </code></pre>
  
  <p>Should return the difference of row 1 compared to row 2:</p>
  
  <pre><code>'{"b":"bbb", "c":"ccc", "d":null}'
  </code></pre>
  
  <p>Instead it returns also duplicates (<code>{"a": "aaa"}</code>). Also; there might be a more elegant approach in general!</p>