Comparer des tableaux JSON dans Postgres

Question

Je dispose d'un tableau représentant les articles d'un panier à différents moments. Les articles du panier sont stockés au format JSON, une colonne avec l'élément before et une autre avec les valeurs after valeurs.

Ma tâche consiste à comparer les paniers et à identifier les articles ajoutés, les articles supprimés et les changements de prix/quantités.

Je sais que je peux utiliser json_array_elements et une jointure croisée pour décompresser les blobs JSON, créant une table d'articles à partir des valeurs JSON. Mais je n'arrive pas à trouver un moyen efficace de comparer les articles du panier. Puis-je le faire en SQL ?

Voici un petit exemple Fidèle à SQL :

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

Cette requête coûteuse ne résout pas mon problème :

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;

Answer 1

Vous pouvez utiliser le except all pour obtenir la différence entre vos deux json, ce qui vous permet de savoir quels éléments ont été ajoutés et supprimés. Pour trouver la différence, ma requête sera :

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)