如何在postgres中合并两个json对象到一个数组

Question

我有两个jsonb值。我想把它们合并，不管我是怎么合并的。这是我的代码

CREATE OR REPLACE FUNCTION emr.azintin(p_msg text)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
DECLARE
v_msgar     text[];
v_msgln     text;
v_currusr   afm_usr.user_id%type := 'SYSEXCH';
v_currdt    afm_usr.insert_date%type := CURRENT_TIMESTAMP;
macres      azt_macres%rowtype;
orddet      lbt_orddet%rowtype;
v_resultid  character varying ;
v_reading   character varying ;
BEGIN   
v_msgar := array['OBR||||^^^3972^2102001420||||20210227133222|25||||3
OBX|||0^RBC||4.99|^10â¶/μL|3.85-5.78||||F
OBX|||1^HGB||14.4|^g/dL|12.0-17.2||||F
OBX|||2^MCV||84.5|^fL|78.0-96.0||||F
OBX|||3^HCT||42.2|^%|34.8-50.9||||F
OBX|||4^MCH||28.90|^pg|26.40-33.20||||F
OBX|||5^MCHC||34.2|^g/dL|31.8-36.7|||<EOT>|F
OBX|||6^RDWsd||39.2|^fL|0.0-0.0||||F'];

SELECT json_agg(jsonb_build_object('resultValue',split_part(parts[6],'^',1)))
INTO v_reading
FROM unnest(v_msgar) as elem,
regexp_split_to_array(elem, '|') as parts
WHERE parts[1] = 'OBX';

SELECT json_agg(jsonb_build_object('resultId',split_part(parts[4],'^',2)))
INTO v_resultid
FROM unnest(v_msgar) as elem,
regexp_split_to_array(elem, '|') as parts
WHERE parts[1] = 'OBX';

select concat(v_resultid || v_reading) 
INTO macres.test_result
FROM unnest(v_msgar) as elem,
regexp_split_to_array(elem, '|') as parts
WHERE parts[1] = 'OBX';

SELECT split_part(items[5], '^', 5)
INTO macres.specimen_id
FROM (
SELECT string_to_array(element, '|') as items
FROM unnest(v_msgar) as t(element)) t   
WHERE items[1] = 'OBR';

END IF;
END
$function$
;

我设法得到v_reading和v_resultid看起来像这样

[{"resultValue": "5.20"}, {"resultValue": "14.2"}, {"resultValue": "81.7"}, {"resultValue": "42.5"}, {"resultValue": "27.20"}, {"resultValue": "33.3"}, {"resultValue": "40.6"}, {"resultValue": "15.9"}, {"resultValue": "188"}, {"resultValue": "8.1"}, {"resultValue": "0.15"}, {"resultValue": "9.4"}, {"resultValue": "46.2"}, {"resultValue": "4.6"}, {"resultValue": "1.1"}, {"resultValue": "23.6"}, {"resultValue": "0.4"}, {"resultValue": "8.7"}, {"resultValue": "3.1"}, {"resultValue": "67.7"}, {"resultValue": "56"}, {"resultValue": "105"}, {"resultValue": ""}] 

[{"resultId": "RBC"}, {"resultId": "HGB"}, {"resultId": "MCV"}, {"resultId": "HCT"}, {"resultId": "MCH"}, {"resultId": "MCHC"}, {"resultId": "RDWsd"}, {"resultId": "RDWcv"}, {"resultId": "PLT"}, {"resultId": "MPV"}, {"resultId": "PCT"}, {"resultId": "PDWsd"}, {"resultId": "PDWcv"}, {"resultId": "WBC"}, {"resultId": "LYM"}, {"resultId": "LYMP"}, {"resultId": "MID"}, {"resultId": "MIDP"}, {"resultId": "GRA"}, {"resultId": "GRAP"}, {"resultId": "PLCR"}, {"resultId": "PLCC"}, {"resultId": "RBCHistogram"}]

如何将这两个json组合成这样

[{"resultid": "RBC", "resultValue": "5.20"}, {"resultid": "HGB", "resultValue": "5.20"}, {"resultid": "MCV", "resultValue": "5.20"}, {"resultid": "HCT", "resultValue": "5.20"}, {"resultid": "MCH", "resultValue": "5.20"}, {"resultid": "MCHC", "resultValue": "5.20"}, {"resultid": "RDWsd", "resultValue": "5.20"}, {"resultid": "RDWcv", "resultValue": "5.20"}, {"resultid": "PLT", "resultValue": "5.20"}, {"resultid": "MPV", "resultValue": "5.20"}, {"resultid": "PCT", "resultValue": "5.20"}, {"resultid": "PDWsd", "resultValue": "5.20"}, {"resultid": "PDWcv", "resultValue": "5.20"}, {"resultid": "WBC", "resultValue": "5.20"}, {"resultid": "LYM", "resultValue": "5.20"}, {"resultid": "LYMP", "resultValue": "5.20"}, {"resultid": "MID", "resultValue": "5.20"}, {"resultid": "MIDP", "resultValue": "5.20"}, {"resultid": "GRA", "resultValue": "5.20"}, {"resultid": "GRAP", "resultValue": "5.20"}, {"resultid": "PLCR", "resultValue": "5.20"}, {"resultid": "PLCC", "resultValue": "5.20"}, {"resultid": "RBCHistogram", "resultValue": "5.20"}]

我可以像这样做

SELECT json_agg(jsonb_build_object('resultid',split_part(parts[4],'^',2), 'resultValue',split_part(parts[6],'^',1)))
INTO macres.test_result
FROM unnest(v_msgar) as elem,
regexp_split_to_array(elem, '|') as parts
WHERE parts[1] = 'OBX';

，但问题是我需要在split_part(parts[4]，'^'，2)中放置一个条件，这就是为什么我将其分为v_reading和v_resultid。

我想写条件

IF v_resultid = 'MCHC' THEN
v_reading = null
END ID

但不知何故，如果数组找到MCHC，它仍然会读取。我想从json中删除MCHC

Answer 1

连接两个JSON对象:

演示:db<在小提琴>

SELECT
jsonb_agg(a.element || b.element)                        -- 4
FROM (
SELECT * FROM jsonb_array_elements(                      -- 1
'[{"resultValue": "5.20"}, {"resultValue": "14.2"}, {"resultValue": "81.7"}, {"resultValue": "42.5"}, {"resultValue": "27.20"}, {"resultValue": "33.3"}, {"resultValue": "40.6"}, {"resultValue": "15.9"}, {"resultValue": "188"}, {"resultValue": "8.1"}, {"resultValue": "0.15"}, {"resultValue": "9.4"}, {"resultValue": "46.2"}, {"resultValue": "4.6"}, {"resultValue": "1.1"}, {"resultValue": "23.6"}, {"resultValue": "0.4"}, {"resultValue": "8.7"}, {"resultValue": "3.1"}, {"resultValue": "67.7"}, {"resultValue": "56"}, {"resultValue": "105"}, {"resultValue": ""}]'::jsonb
) WITH ORDINALITY as a(element, index)                   -- 2
) a
JOIN (
SELECT * FROM jsonb_array_elements(
'[{"resultId": "RBC"}, {"resultId": "HGB"}, {"resultId": "MCV"}, {"resultId": "HCT"}, {"resultId": "MCH"}, {"resultId": "MCHC"}, {"resultId": "RDWsd"}, {"resultId": "RDWcv"}, {"resultId": "PLT"}, {"resultId": "MPV"}, {"resultId": "PCT"}, {"resultId": "PDWsd"}, {"resultId": "PDWcv"}, {"resultId": "WBC"}, {"resultId": "LYM"}, {"resultId": "LYMP"}, {"resultId": "MID"}, {"resultId": "MIDP"}, {"resultId": "GRA"}, {"resultId": "GRAP"}, {"resultId": "PLCR"}, {"resultId": "PLCC"}, {"resultId": "RBCHistogram"}]'::jsonb
) WITH ORDINALITY as b(element, index)
) b
ON a.index = b.index                                         -- 3

1. 将JSON数组提取为每个元素一行
2. WITH ORDINALITY向每个扩展记录添加行计数，以确定数组中的原始位置
3. 在索引
上连接两个提取的JSON元素集
4. 使用||操作符合并两个JSON列(仅适用于类型jsonb和类型json，但应该使用)。最后，你可以将这些合并的对象聚合成一个新的JSON数组。