sql - Comparing JSON arrays in Postgres -

i have table representing items in cart @ different moments in time. cart items stored in json format, 1 column before values , after values.

my tasks compare carts , identify: items added, items removed , price/quantity changes.

i know can use json_array_elements , cross join unpack json blobs, creating table of items json values. can't think of efficient way compare cart items. can in sql?

here small example sql fiddle:

create table t (   id     int,   before json,   after  json );  insert 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}]' ); 

this expensive query not solve problem:

select   id,   b.value->>'id' id_before,   b.value->>'category' category_before,   b.value->>'price' price_before,   b.value->>'quantity' quantity_before,   a.value->>'id' id_after,   a.value->>'category' category_after,   a.value->>'price' price_after,   a.value->>'quantity' quantity_after t cross join json_array_elements(before) b cross join json_array_elements(after) a; 

you can use except difference between 2 json, can item added , deleted. finding difference query :

select  value->>'id',  value->>'category',  value->>'price',  value->>'quantity'   json_array_elements(before) except select  value->>'id',  value->>'category',  value->>'price',  value->>'quantity'   json_array_elements(after)