40 ms·
One thing I like is when getting data from multiple tables. Let's say you have a product with multiple labels: usually you'll get one line per (product, label)
by arkh 8y ago
One thing I like is when getting data from multiple tables.
Let's say you have a product with multiple labels: usually you'll get one line per (product, label) tuple so you'll have to do some job application side if you what to get one [product, labels] object per product.
With the json aggregate function you can get a (product, json array of labels) tuple per product and just have to do some json_decode in your application code.
- darkpuma 8y ago> "Let's say you have a product with multiple labels: usually you'll get one line per (product, label) tuple so you'll have to do some job application side if you what to get one [product, labels] object per product." What's the payoff of doing [product, labels], particularly if that means a json array of labels? I briefly investigated doing that for my application but I found that it would increase disk space (not really a big problem) but it would also make efficient querying a big chore (e.g. labels->products queries.) I can see this being a good tradeoff if querying by labels is extremely rare and instead you only ever want to query product->labels. But that's still pretty damn fast with the (product, label) tuple schema.
- arkh 8y agoYou don't change your schema to add some json field. When querying you do something like select p.name, json_agg(l.value) as labels from product p, label l