SQL to get Products and related data from PostgreSQL

The objective of this exercise for me was to dump all the product data as CSV / JSON so I could index them into Algolia. The exercise consisted of multiple steps to get to the required form of data. I will run through all those steps and explain what is happening.

Here is an example query to query for products from the table Product and its related category and image from respective tables.

Joining multiple tables, and getting unique products

SELECT 
  DISTINCT p.id, 
  p.name, 
  p.description,
  c.name AS category,
  i.image,
  p.price, 
  p.stock
FROM 
  "Product" p, 
  "ProductCategory" c, 
  "ProductImage" i,
  "_Product_productCategories_ProductCategory_products" pc, 
  "_Product_photo_ProductImage_product" pi
where 
  p.id = pc."A" and c.id = pc."B" and p.id = pi."A" and i.id = pi."B"
LIMIT 100

I was back to SQL after almost 10 years of break. I struggled quite a bit with those columns like A and B. I could not just use tableAlias.column eg. p.id for them turns out in PostgreSQL, columns with capital letters need to be in double quotes like pc."A" instead of pc.A

The above query will return unique products because of DISTINCT p.id in the beginning, this will ignore multiple relations with ProductCategory and ProductImage.

Aggregating columns to json array

SELECT 
  p.id as objectID, 
  p.name, 
  p.description, 
  json_agg(c.name) AS category, 
  json_agg(i.image), 
  p.price, 
  p.stock
FROM 
  "Product" p, 
  "ProductCategory" c, 
  "ProductImage" i,
  "_Product_productCategories_ProductCategory_products" pc, 
  "_Product_photo_ProductImage_product" pi
where 
  p.id = pc."A" and c.id = pc."B" and p.id = pi."A" and i.id = pi."B" 
group by p.id
LIMIT 100

Before this, when working with MySQL, I had only aggregated strings with simple string concatenation with a delimiter, JSON was not a BIG thing back then anyway, but it’s nice that we can use json_agg(arg) which aggregates column as a JSON array. This can be further customized with json_build_object() which helps you build JSON objects with multiple fields and aggregate those objects as an array.

Extracting fields from JSON columns

SELECT 
  p.id as objectID, 
  p.name, 
  p.description, 
  json_agg(c.name) AS category, 
  json_agg(json_extract_path_text(i.image::json, '_meta', 'url')), 
  p.price, 
  p.stock
FROM 
  "Product" p, 
  "ProductCategory" c, 
  "ProductImage" i,
  "_Product_productCategories_ProductCategory_products" pc, 
  "_Product_photo_ProductImage_product" pi
where 
  p.id = pc."A" and c.id = pc."B" and p.id = pi."A" and i.id = pi."B" 
group by p.id
LIMIT 100

I was using Cloudinary for images, and the database stored JSON objects of those image data. The function, json_extract_path_text() helps you extract those fields as a string. On the above example, it gets Object._meta.url