By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE diary
(id int, user_id int, custom_foods text)
;
INSERT INTO diary
(id, user_id, custom_foods)
VALUES
(1, 1, '{"56": 2, "42": 0}'),
(2, 1, '{"19861": 1}'),
(3, 2, '{}'),
(4, 3, '{"331": 0}')
;
4 rows affected
WITH empty AS
( SELECT DISTINCT user_id,
0 AS COUNT
FROM diary
WHERE custom_foods = '{}' )
SELECT user_id,
count(CASE
WHEN VALUE::int > 0 THEN 1
END)
FROM diary d,
json_each_text(d.custom_foods::JSON)
GROUP BY user_id
UNION ALL
SELECT *
FROM empty
ORDER BY user_id;
| user_id | count |
|---|---|
| 1 | 2 |
| 2 | 0 |
| 3 | 0 |