-- An outer join of an unnested JSON function keeps the rows without a member (NULL columns) and applies its ON clause to the members: the query stays native.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE facts (id int, dim_a_id int, metrics jsonb, tags_a jsonb);
INSERT INTO facts
SELECT i, i % 40,
       CASE i % 9 WHEN 0 THEN '{}'::jsonb WHEN 1 THEN '{"m_total": null}'::jsonb WHEN 2 THEN '{"other": 3}'::jsonb WHEN 3 THEN '{"m_total": 6.5, "other": 9}'::jsonb
                  WHEN 4 THEN NULL ELSE jsonb_build_object('m_total', i % 10, 'other', i % 3) END,
       CASE i % 4 WHEN 0 THEN NULL WHEN 1 THEN '[]'::jsonb WHEN 2 THEN '[{"type":"type_a"}]'::jsonb ELSE '[{"type":"type_c"},{"type":"type_a"}]'::jsonb END
FROM generate_series(1, 20000) i;
CREATE INDEX facts_col ON facts USING sift_col (id, dim_a_id, metrics, tags_a) WITH (paths = 'metrics.* float8, tags_a[]{type text}');
NOTICE:  sift_col index "facts_col": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('facts_col');
VACUUM (FREEZE, ANALYZE) facts;
-- The result, or the SQLSTATE and message, with the scan off and on. on: the table scans are off, so a Custom Scan (SiftAgg) runs when the query is one it can run.
\ir test/agg_helpers.sql
\set ECHO none
CREATE FUNCTION check_query(name text, q text) RETURNS TABLE (label text, scan bool, same bool, outcome text) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT name, runs_in_scan(q), outcome(q, false) = outcome(q, true), left(outcome(q, true), 100);
END $$;
SELECT * FROM check_query('left join, on', $q$
 SELECT count(*) AS n, count(r.key) AS dim_b, count(DISTINCT f.id) AS ids
 FROM facts f LEFT JOIN LATERAL jsonb_each(f.metrics) r ON r.key = 'm_total'
$q$);
     label     | scan | same |                   outcome                    
---------------+------+------+----------------------------------------------
 left join, on | f    | t    | [{"n": 20000, "ids": 20000, "dim_b": 13333}]
(1 row)

SELECT * FROM check_query('left join, true', $q$
 SELECT r.key, count(*) AS n FROM facts f LEFT JOIN LATERAL jsonb_each(f.metrics) r ON true GROUP BY 1
$q$);
      label      | scan | same |                                         outcome                                          
-----------------+------+------+------------------------------------------------------------------------------------------
 left join, true | f    | t    | [{"n": 13333, "key": "m_total"}, {"n": 13333, "key": "other"}, {"n": 4444, "key": null}]
(1 row)

SELECT * FROM check_query('left join, array', $q$
 SELECT count(*) AS n, count(e) AS elements, count(DISTINCT f.id) AS ids
 FROM facts f LEFT JOIN LATERAL jsonb_array_elements(f.tags_a) e ON e->>'type' = 'type_a'
$q$);
      label       | scan | same |                     outcome                     
------------------+------+------+-------------------------------------------------
 left join, array | f    | t    | [{"n": 20000, "ids": 20000, "elements": 10000}]
(1 row)

SELECT * FROM check_query('inner join, on', $q$
 SELECT count(*) AS n, count(DISTINCT f.id) AS ids FROM facts f JOIN LATERAL jsonb_each(f.metrics) r ON r.key = 'm_total'
$q$);
     label      | scan | same |           outcome            
----------------+------+------+------------------------------
 inner join, on | t    | t    | [{"n": 13333, "ids": 13333}]
(1 row)

DROP FUNCTION check_query(text, text), outcome(text, bool), runs_in_scan(text), pushed, runq;
DROP TABLE facts;
DROP EXTENSION pg_sift;