-- jsonb_exists(doc, 'k') of the document whose list is unnested: the rows of the list exist only for an object (the list is read from it), whose
-- key the index holds as the type of the member k (a JSON null member exists, a missing one does not). Elsewhere a document may be an array
-- holding the string or the string itself, which the index does not tell: those queries stay with PostgreSQL.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
SET seq_page_cost = 10000; SET random_page_cost = 10000;
CREATE TABLE oe_docs (id int PRIMARY KEY, set_id int, doc jsonb);
INSERT INTO oe_docs SELECT i, i % 5,
  CASE i % 9
    WHEN 0 THEN jsonb_build_object('flag_a', true, 'items', jsonb_build_array(jsonb_build_object('category', 'a', 'score', i % 10), jsonb_build_object('category', 'b', 'score', 3)))
    WHEN 1 THEN jsonb_build_object('flag_a', NULL, 'items', jsonb_build_array(jsonb_build_object('category', 'a', 'score', 5)))
    WHEN 2 THEN jsonb_build_object('items', jsonb_build_array(jsonb_build_object('category', 'b', 'score', i % 7)))
    WHEN 3 THEN jsonb_build_array('flag_a', jsonb_build_object('items', 1))
    WHEN 4 THEN to_jsonb('flag_a'::text)
    WHEN 5 THEN 'null'::jsonb
    WHEN 6 THEN to_jsonb(i)
    WHEN 7 THEN NULL
    ELSE jsonb_build_object('flag_a', 'x', 'items', '[]'::jsonb)
  END
FROM generate_series(1, 4500) i;
CREATE INDEX oe_docs_col ON oe_docs USING sift_col (id, set_id, doc) WITH (paths = 'doc.flag_a astext, doc.items[]{category text, score float8}');
NOTICE:  sift_col index "oe_docs_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('oe_docs_col');
VACUUM (FREEZE, ANALYZE) oe_docs;
\ir test/agg_check_pushed.sql
\set ECHO none
-- a key and an aggregate argument that depend on the key of the document, for the rows of its list
SELECT * FROM check_query($q$
 SELECT d.set_id, CASE WHEN jsonb_exists(d.doc, 'flag_a') THEN o.category ELSE 'raw' END AS category, count(*) AS n, avg(o.score) AS score
 FROM oe_docs d CROSS JOIN LATERAL jsonb_to_recordset(d.doc['items']) AS o(category text, score float8) GROUP BY 1, 2
$q$);
 pushed | differences 
--------+-------------
 t      |           0
(1 row)

-- a filter
SELECT * FROM check_query($q$
 SELECT o.category, count(*) FILTER (WHERE NOT jsonb_exists(d.doc, 'flag_a')) AS raw, count(*) AS n
 FROM oe_docs d CROSS JOIN LATERAL jsonb_to_recordset(d.doc['items']) AS o(category text, score float8) GROUP BY 1
$q$);
 pushed | differences 
--------+-------------
 t      |           0
(1 row)

-- the documents themselves: an array or a string holding the key exists, the index cannot tell
SELECT * FROM check_query($q$
 SELECT d.set_id, jsonb_exists(d.doc, 'flag_a') AS has_key, count(*) AS n FROM oe_docs d GROUP BY 1, 2
$q$);
 pushed | differences 
--------+-------------
 f      |           0
(1 row)

DROP FUNCTION check_query(text);
DROP TABLE oe_docs;
DROP EXTENSION pg_sift;