Rewrite_Query.sql

My tests:

CREATE TABLE flows(id integer, app_protocol_details jsonb);

SELECT create_distributed_table('flows','id');

Query which din't run:

select app_protocol_details from flows fu, jsonb_array_elements(fu.app_protocol_details->'protocols') protocols where protocols->>'{dnsProtocol,rc}' like '%ERROR%';

Attempt #1:(Failed)
set citus.subquery_pushdown to on;

SELECT a FROM (select app_protocol_details a,id from flows fu, jsonb_array_elements(fu.app_protocol_details->'protocols') protocols where protocols->>'{dnsProtocol,rc}' like '%ERROR%' GROUP BY id, app_protocol_details) a;

ERROR:  cannot push down this subquery
DETAIL:  Table expressions other than simple relations and subqueries are currently unsupported

Attempt #2:(Failed)
SELECT citus_run_on_all_placements('flows','select app_protocol_details from %I fu, jsonb_array_elements(fu.app_protocol_details->''protocols'') protocols where protocols->>''{dnsProtocol,rc}'' like ''%ERROR%'';',true);

ERROR:  unrecognized conversion type specifier "E"
CONTEXT:  SQL statement "WITH citus_placements AS (
		SELECT
			ds.logicalrelid::regclass AS tablename,
			ds.shardid AS shardid,
			shard_name(ds.logicalrelid, ds.shardid) AS shardname,
			dsp.nodename AS nodename, dsp.nodeport::int AS nodeport
		FROM pg_dist_shard ds JOIN pg_dist_shard_placement dsp USING (shardid)
		WHERE dsp.shardstate = 1 and ds.logicalrelid::regclass = table_name
		ORDER BY ds.logicalrelid, ds.shardid, dsp.nodename, dsp.nodeport)
	SELECT
		array_agg(cp.nodename), array_agg(cp.nodeport), array_agg(cp.shardid),
		array_agg(format(command, cp.shardname))
	                                       FROM citus_placements cp"
PL/pgSQL function citus_run_on_all_placements(regclass,text,boolean) line 8 at SQL statement


Attempt #3: (Success)
Created function on master and workers and and rewrote the query. Idea might be similar please check semantics.
CREATE OR REPLACE FUNCTION check_error (app_prot jsonb)
RETURNS boolean
AS $$
DECLARE
    i jsonb;
BEGIN
  FOR i IN SELECT * FROM jsonb_array_elements(app_prot->protocols)
  LOOP
    IF i->>>'{dnsProtocol,rc}' like '%ERROR%' THEN
        RETURN true;
    END IF;
  END LOOP;
  RETURN false;
END;
$$ language plpgsql

select app_protocol_details from flows fu WHERE check_error(fu.app_protocol_details);