如何循环 json 数据并计算 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$;

我正在获取以下文件数据

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"}"}

现在我想迭代这个文件数据并获取每个fc_primary_key值并检查它在整个 json 数据中出现的次数

注意：每个fc_primary_key只需使用其后面的值进行验证。它不应与之前的fc_primary键进行比较。

例如，如果我检查第三个元素"A1234567892017/08/07"，它会在其位置后出现 4 次。所以计数必须是 4。

第七元素中存在相同的"A1234567892017/08/07"，但在第七位之后不再有"A1234567892017/08/07"。所以计数必须为零"0">

如何循环数据并获取计数，因为我是帖子的新手，因此无法找到解决方案。请帮忙！！

Answer 1

我能够用下面的代码得到你描述的结果。通过取消嵌套数据，您可以利用常规SQL语法(偏移，分组，计数(，这是您所描述问题的症结所在。

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;

或者，您可以使用窗口而不是偏移量在单个语句中获取整个数据集(如果这会有所帮助(。

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;