I don't know if postgres allows one to treat boolean as 0 and 1 natively but in t-sql, the equivalent would be something like:<p><pre><code> DECLARE @orders TABLE (
category VARCHAR(5),
express_delivered INT
);
INSERT INTO @orders (category, express_delivered) VALUES ( 'food', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'food', 0);
INSERT INTO @orders (category, express_delivered) VALUES ( 'shoes',0);
INSERT INTO @orders (category, express_delivered) VALUES ( 'shoes',0);
INSERT INTO @orders (category, express_delivered) VALUES ( 'auto', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'auto', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'book', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'book', 0);
SELECT
category,
CASE WHEN SUM(express_delivered) = 0 THEN 0 ELSE 1 END AS ever_been_express_delivered
FROM @orders
GROUP BY category</code></pre>
Very cool. It looks like you can also use `every()` instead of `bool_and()` - and also note that you can provide any boolean value to these, so you can do something like `every(column = 'value')...`
I love `json_build_object()`.<p>With `json_build_object()`, you can select fields as a JSON object and not have to do that JSON building in your app.<p>Here's a snippet demonstrating building, and then querying a JSON object:<p><pre><code> with location as (select json_build_object(
'street1', street1,
'street2', street2,
'city', city,
'state', state,
'zip', zip
) as loc from my_table)
select
loc ->> 'street1' as street1,
loc ->> 'street2' as street2,
loc ->> 'city' as city,
loc ->> 'state' as state,
loc ->> 'zip' as zip
from location;</code></pre>
Doesn't this become less valueable as the data grows? You'll essentially always have at least one "true" value, and at that point you're basically doing the query of:<p>SELECT cateogry FROM orders WHERE
express_delivery=TRUE
GROUP BY category<p>Also are these aggregate functions as efficient?