PostgreSQL中遍历JSON数据并统计后续重复值的实现方法

Got it, let's tackle this problem step by step. First, I notice a small oversight in your original function: you're using f_primary_key without declaring it in the DECLARE section—we'll fix that first. Then, to count how many times each fc_primary_key appears after its current position in the filedata array, we'll break this into manageable steps.

Step 1: Fix the Original Function & Extract Primary Keys #

First, let's update your function to properly declare variables and extract the raw fc_primary_key values into a dedicated array (this makes counting easier):

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;
    f_primary_key text; -- Added missing variable declaration
    i INT;
    -- New variables for counting logic
    key_values text[];
    current_key text;
    count_after int;
    j int;
BEGIN 
    -- Original logic to build filedata array
    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_call_receipt; -- Fixed: referenced undefined f_auth_clm_number
        filedata[i] := jsonb_build_object( 'fc_primary_key',f_primary_key ); 
        i := i+1; 
    END LOOP;

    -- Extract fc_primary_key values from filedata into a simple text array
    key_values := ARRAY(
        SELECT (jsonb_parse_element_text(elem) ->> 'fc_primary_key')
        FROM unnest(filedata) AS elem
    );

    -- Step 2: Count trailing occurrences for each key
    FOR i IN 1..array_length(key_values, 1) LOOP
        current_key := key_values[i];
        count_after := 0;
        -- Loop through all elements AFTER the current index
        FOR j IN i+1..array_length(key_values, 1) LOOP
            IF key_values[j] = current_key THEN
                count_after := count_after + 1;
            END IF;
        END LOOP;
        -- Print the result (adjust this to return data if needed)
        RAISE NOTICE 'Position: %, Primary Key: %, Occurrences after: %', i, current_key, count_after;
    END LOOP;

    RETURN 'Success'; -- Return a status message
END; 
$BODY$;

Key Fixes & Notes: #

• Added the missing f_primary_key variable declaration.
• Fixed a typo: you referenced f_auth_clm_number which wasn't declared—assuming this was meant to be f_call_receipt based on your code context.
• Converted the filedata array of JSON strings into a simple text[] array of just fc_primary_key values to simplify counting.

Alternative: More Efficient SQL-Based Approach #

If you're working with larger datasets, a nested loop might be slow. Instead, use a temporary table and self-join to calculate counts with SQL:

-- Inside the same function, replace the nested loop section with this:
DECLARE
    -- ... keep existing variables ...
BEGIN
    -- ... keep original filedata generation logic ...

    -- Create a temporary table to store positions and keys
    CREATE TEMP TABLE IF NOT EXISTS key_positions (
        idx INT,
        primary_key TEXT
    );
    TRUNCATE key_positions; -- Clear table if it exists from previous runs

    -- Populate the temporary table
    INSERT INTO key_positions (idx, primary_key)
    SELECT 
        generate_subscripts(filedata, 1) AS idx,
        (jsonb_parse_element_text(elem) ->> 'fc_primary_key') AS primary_key
    FROM unnest(filedata) AS elem;

    -- Query to get trailing counts
    FOR i, current_key, count_after IN
        SELECT 
            kp.idx,
            kp.primary_key,
            COUNT(kp2.idx) AS occurrences_after
        FROM key_positions kp
        LEFT JOIN key_positions kp2 
            ON kp.primary_key = kp2.primary_key 
            AND kp2.idx > kp.idx
        GROUP BY kp.idx, kp.primary_key
        ORDER BY kp.idx
    LOOP
        RAISE NOTICE 'Position: %, Primary Key: %, Occurrences after: %', i, current_key, count_after;
    END LOOP;

    RETURN 'Success';
END;

How It Works for Your Sample Data #

For your provided filedata array:

• The 3rd element (A1234567892017/08/07) will show 4 occurrences after it.
• The 7th element (same key) will show 0 occurrences after it.
• All other elements will show their respective trailing counts based on the array content.

内容的提问来源于stack exchange，提问作者Sai sri