Comparing JSON arrays in Postgres

Question

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 before values and another with the after values.

My tasks is compare the carts and identify: items added, items removed and price/quantity changes.

I know I can use json_array_elements 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?

Here is a small example SQL Fiddle:

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}]'
);

This expensive query does not solve my problem:

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

Ashish Singh · Accepted Answer · 2017-04-12 07:25:29Z

2

You can use the except all to get the difference between your two json, by this you can get which item are added and deleted. For finding the difference my query will be :

select
 value->>'id',
 value->>'category',
 value->>'price',
 value->>'quantity'
FROM
  json_array_elements(before)
EXCEPT ALL
select
 value->>'id',
 value->>'category',
 value->>'price',
 value->>'quantity'
FROM
  json_array_elements(after)

answered Apr 12, 2017 at 7:25

Ashish Singh

363 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Comparing JSON arrays in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related