How to Loop json Data and count the values in postgres

Question

CREATE OR REPLACE FUNCTION file_compare()
RETURNS text LANGUAGE 'plpgsql'
COST 100 VOLATILE AS $BODY$
DECLARE

filedata text[];
fpo_data jsonb;
inddata jsonb;
f_cardholderid text;
f_call_receipt text;
i INT;

BEGIN
  SELECT json_agg((fpdata))::jsonb
  FROM (SELECT fo_data AS fpdata
        FROM fpo
        LIMIT 100
    ) t  INTO fpo_data; 
    i=0;

    FOR inddata IN SELECT * FROM jsonb_array_elements(fpo_data) LOOP

    f_cardholderid := (inddata->>0)::JSONB->'cardholder_id'->>'value';
    f_call_receipt := (inddata->>0)::JSONB->'call_receipt_date'->>'value';

    f_primary_key := f_cardholderid || f_auth_clm_number;

    filedata[i] := jsonb_build_object(
        'fc_primary_key',f_primary_key
    );
    i := i+1;

    END LOOP;   

    RAISE NOTICE 'PRINTING DATA %', filedata;
    END;
$BODY$;

I am getting the filedata as below

 NOTICE:  PRINTING DATA ={"{\"fc_primary_key\": \"A1234567892017/06/27\"}","{\"fc_primary_key\": \"A1234567892017/06/27\"}","{\"fc_primary_key\": \"A1234567892017/08/07\"}","{\"fc_primary_key\": \"A1234567892017/08/07\"}","{\"fc_primary_key\": \"A1234567892017/08/07\"}","{\"fc_primary_key\": \"A1234567892017/08/07\"}","{\"fc_primary_key\": \"A1234567892017/08/07\"}","{\"fc_primary_key\": \"A1234567892024/03/01\"}","{\"fc_primary_key\": \"A12345678945353\"}","{\"fc_primary_key\": \"A1234567892023/11/22\"}","{\"fc_primary_key\": \"A12345678945252\"}","{\"fc_primary_key\": \"A1234567892017-07-01\"}"}

Now I want to iterate this filedata and get each fc_primary_key value and check the count how many times it appeared in entire json data

Note: Each fc_primary_key has to be verified only with the values which are present after it. It should not compare with the fc_primary keys before it.

For example if I check the third element which is "A1234567892017/08/07", it appeared 4 times after its position. So the count must be 4.

Where as the same "A1234567892017/08/07" is there in seventh element, but there are no more "A1234567892017/08/07" after seventh position. So the count must be zero "0"

How do I loop the data and get the count, as I am new to postgres I am unable to find the solution. Please help!!

Answer 1

I was able to get the result you describe with the code below. By unnesting the data, you are able to take advantage of regular SQL syntax (offset, grouping, counting) which are the crux of the problem you described.

DO
$body$
   DECLARE
      fildata TEXT[] = ARRAY ['{''fc_primary_key'': ''A1234567892017/06/27''}','{''fc_primary_key'': ''A1234567892017/06/27''}','{''fc_primary_key'': ''A1234567892017/08/07''}','{''fc_primary_key'': ''A1234567892017/08/07''}','{''fc_primary_key'': ''A1234567892017/08/07''}','{''fc_primary_key'': ''A1234567892017/08/07''}','{''fc_primary_key'': ''A1234567892017/08/07''}','{''fc_primary_key'': ''A1234567892024/03/01''}','{''fc_primary_key'': ''A12345678945353''}','{''fc_primary_key'': ''A1234567892023/11/22''}','{''fc_primary_key'': ''A12345678945252''}','{''fc_primary_key'': ''A1234567892017-07-01''}'];
      count   INTEGER;
   BEGIN
      FOR i IN 1 .. array_length(fildata, 1) LOOP
         SELECT count(*) - 1
           INTO count
           FROM (
                   SELECT unnest(fildata) AS x OFFSET (i - 1)
                ) AS t
          WHERE x = fildata[i]
          GROUP BY x;

         RAISE NOTICE 'Row % appears % times after the current', fildata[i], count;
      END LOOP;
   END
$body$ LANGUAGE plpgsql;

Alternatively, you can get the entire set of data in a single statement (if that would be helpful) by using windowing instead of offset.

SELECT t
     , count(*) OVER (PARTITION BY t ORDER BY rn RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - 1 AS count
  FROM (
          SELECT row_number() OVER () AS rn, t
            FROM unnest(
                       ARRAY ['{''fc_primary_key'': ''A1234567892017/06/27''}','{''fc_primary_key'': ''A1234567892017/06/27''}','{''fc_primary_key'': ''A1234567892017/08/07''}','{''fc_primary_key'': ''A1234567892017/08/07''}','{''fc_primary_key'': ''A1234567892017/08/07''}','{''fc_primary_key'': ''A1234567892017/08/07''}','{''fc_primary_key'': ''A1234567892017/08/07''}','{''fc_primary_key'': ''A1234567892024/03/01''}','{''fc_primary_key'': ''A12345678945353''}','{''fc_primary_key'': ''A1234567892023/11/22''}','{''fc_primary_key'': ''A12345678945252''}','{''fc_primary_key'': ''A1234567892017-07-01''}']) AS t
       ) AS x
 ORDER BY rn;