I can't create a function in PGSQL via concat to get data in a loop over all json. But I want to get data from the database for this json in a loop through all the data from metrics->values->avg and med Let's say from http_req_duration{scenario:find}->metrics->values->avg and med
JSON structure is like this.
{
"root_group": {
"name": "",
"path": "",
"id": "d41d8cd98f00b204e9800998ecf8427e",
"groups": [],
"checks": [
{
"name": "is joint 200",
"path": "::is joint 200",
"id": "ae8070af06d4849b5a73c0ea09c2238e",
"passes": 12122,
"fails": 0
},
{
"name": "is status 200 find-all",
"path": "::is status 200 find-all",
"id": "bae23285599010636d5dcbc6311a79b6",
"passes": 73852,
"fails": 0
},
{
"passes": 30874,
"fails": 3395,
"name": "is status 200 conditional",
"path": "::is status 200 conditional",
"id": "8f2e67a51bef6111123e1aa04f006a1c"
}
]
},
"options": {
"summaryTrendStats": [
"avg",
"min",
"med",
"max",
"p(90)",
"p(95)"
],
"summaryTimeUnit": "",
"noColor": false
},
"state": {
"isStdOutTTY": false,
"isStdErrTTY": false,
"testRunDurationMs": 345617.874234
},
"metrics": {
"data_received": {
"contains": "data",
"values": {
"count": 106316067,
"rate": 307611.59918488155
},
"type": "counter"
},
"http_req_waiting{scenario:prePubEventsDriverStart}": {
"type": "trend",
"contains": "time",
"values": {
"avg": 0,
"min": 0,
"med": 0,
"max": 0,
"p(90)": 0,
"p(95)": 0
},
"thresholds": {
"p(95)<=2": {
"ok": true
},
"avg <= 3": {
"ok": true
},
"med <= 1": {
"ok": true
},
"p(90)<=2": {
"ok": true
}
}
},
"http_req_blocked{scenario:find}": {
"type": "trend",
"contains": "time",
"values": {
"min": 0.001723,
"med": 0.004228,
"max": 9.42929,
"p(90)": 0.005511,
"p(95)": 0.006082,
"avg": 0.00487203336402536
},
"thresholds": {
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
},
"avg <= 1": {
"ok": true
},
"med <= 1": {
"ok": true
}
}
},
"http_req_duration{scenario:eventsDriverStop}": {
"thresholds": {
"p(95)<=8": {
"ok": true
},
"avg <= 4": {
"ok": true
},
"med <= 4": {
"ok": true
},
"p(90)<=7": {
"ok": true
}
},
"type": "trend",
"contains": "time",
"values": {
"min": 0,
"med": 0,
"max": 0,
"p(90)": 0,
"p(95)": 0,
"avg": 0
}
},
"http_req_waiting{scenario:con}": {
"contains": "time",
"values": {
"med": 0.936649,
"max": 99.55948,
"p(90)": 2.4427536,
"p(95)": 4.682848199999995,
"avg": 1.5082012875777009,
"min": 0.541066
},
"thresholds": {
"avg <= 3": {
"ok": true
},
"med <= 1": {
"ok": true
},
"p(90)<=4": {
"ok": true
},
"p(95)<=5.8": {
"ok": true
}
},
"type": "trend"
},
"http_req_receiving{scenario:find}": {
"values": {
"max": 15.097094,
"p(90)": 0.066625,
"p(95)": 0.08054044999999994,
"avg": 0.05262911127660761,
"min": 0.013265,
"med": 0.046247
},
"thresholds": {
"avg <= 1": {
"ok": true
},
"med <= 1": {
"ok": true
},
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
}
},
"type": "trend",
"contains": "time"
},
"iterations{scenario:eventsDriverStop}": {
"values": {
"count": 1201,
"rate": 3.4749360190406846
},
"thresholds": {
"count>0": {
"ok": true
}
},
"type": "counter",
"contains": "default"
},
"vus_max": {
"type": "gauge",
"contains": "default",
"values": {
"value": 249,
"min": 114,
"max": 249
}
},
"dropped_iterations": {
"type": "counter",
"contains": "default",
"values": {
"count": 26803,
"rate": 77.55096596032263
}
},
"http_req_duration{scenario:find}": {
"type": "trend",
"contains": "time",
"values": {
"avg": 1.3326389626144177,
"min": 0.446288,
"med": 0.7976185,
"max": 84.594877,
"p(90)": 2.3167139000000017,
"p(95)": 3.87368795
},
"thresholds": {
"p(95)<=5": {
"ok": true
},
"avg <= 5": {
"ok": true
},
"med <= 3": {
"ok": true
},
"p(90)<=3.5": {
"ok": true
}
}
},
"http_req_sending{scenario:prePubEventsDriverStart}": {
"type": "trend",
"contains": "time",
"values": {
"max": 0,
"p(90)": 0,
"p(95)": 0,
"avg": 0,
"min": 0,
"med": 0
},
"thresholds": {
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
},
"avg <= 1": {
"ok": true
},
"med <= 1": {
"ok": true
}
}
},
"iteration_duration{scenario:con}": {
"type": "trend",
"contains": "time",
"values": {
"p(95)": 612.0240284,
"avg": 603.5558696238289,
"min": 15.970423,
"med": 607.863974,
"max": 657.394861,
"p(90)": 610.6949792
},
"thresholds": {
"avg <= 610": {
"ok": true
},
"med <= 610": {
"ok": true
},
"p(90)<=615": {
"ok": true
},
"p(95)<=620": {
"ok": true
}
}
},
"checks": {
"type": "rate",
"contains": "default",
"values": {
"rate": 0.9717655081792703,
"passes": 116848,
"fails": 3395
}
},
"http_req_sending": {
"type": "trend",
"contains": "time",
"values": {
"med": 0.022442,
"max": 3.47231,
"p(90)": 0.030297,
"p(95)": 0.032682,
"avg": 0.02284295450878623,
"min": 0.006382
}
},
"http_req_receiving{scenario:prePubEventsDriverStart}": {
"type": "trend",
"contains": "time",
"values": {
"p(95)": 0,
"avg": 0,
"min": 0,
"med": 0,
"max": 0,
"p(90)": 0
},
"thresholds": {
"avg <= 1": {
"ok": true
},
"med <= 1": {
"ok": true
},
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
}
}
},
"http_req_sending{scenario:con}": {
"contains": "time",
"values": {
"avg": 0.02357408380752288,
"min": 0.008426,
"med": 0.023204,
"max": 0.356089,
"p(90)": 0.031048,
"p(95)": 0.033433
},
"thresholds": {
"med <= 1": {
"ok": true
},
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
},
"avg <= 1": {
"ok": true
}
},
"type": "trend"
},
"iteration_duration{scenario:eventsDriverStop}": {
"type": "trend",
"contains": "time",
"values": {
"avg": 1.9972035187343873,
"min": 1.141805,
"med": 1.685224,
"max": 16.105638,
"p(90)": 2.205132,
"p(95)": 5.332459
},
"thresholds": {
"p(95)<=7": {
"ok": true
},
"avg <= 3": {
"ok": true
},
"med <= 3": {
"ok": true
},
"p(90)<=3": {
"ok": true
}
}
},
"http_req_duration{scenario:eventsDriverStart}": {
"type": "trend",
"contains": "time",
"values": {
"min": 0,
"med": 0,
"max": 0,
"p(90)": 0,
"p(95)": 0,
"avg": 0
},
"thresholds": {
"p(90)<=7": {
"ok": true
},
"p(95)<=8": {
"ok": true
},
"avg <= 4": {
"ok": true
},
"med <= 4": {
"ok": true
}
}
},
"http_req_duration{expected_response:true}": {
"type": "trend",
"contains": "time",
"values": {
"max": 99.649419,
"p(90)": 2.3154703000000003,
"p(95)": 3.958521399999999,
"avg": 1.3774202878354742,
"min": 0.446288,
"med": 0.848834
}
},
"iteration_duration{scenario:cachUpdate}": {
"type": "trend",
"contains": "time",
"values": {
"p(90)": 1.945155,
"p(95)": 5.37025,
"avg": 1.9303337557073827,
"min": 1.010889,
"med": 1.637776,
"max": 13.960856
},
"thresholds": {
"avg <= 3": {
"ok": true
},
"med <= 3": {
"ok": true
},
"p(90)<=3": {
"ok": true
},
"p(95)<=7": {
"ok": true
}
}
},
"http_req_duration{scenario:join}": {
"type": "trend",
"contains": "time",
"values": {
"p(90)": 1.8037868999999997,
"p(95)": 3.272206999999999,
"avg": 1.1918320848869903,
"min": 0.449915,
"med": 0.740105,
"max": 76.250356
},
"thresholds": {
"p(95)<=4": {
"ok": true
},
"avg <= 5": {
"ok": true
},
"med <= 3": {
"ok": true
},
"p(90)<=2.5": {
"ok": true
}
}
},
"http_req_receiving{scenario:cachUpdate}": {
"contains": "time",
"values": {
"max": 0,
"p(90)": 0,
"p(95)": 0,
"avg": 0,
"min": 0,
"med": 0
},
"thresholds": {
"p(95)<=1": {
"ok": true
},
"avg <= 1": {
"ok": true
},
"med <= 4": {
"ok": true
},
"p(90)<=1": {
"ok": true
}
},
"type": "trend"
},
"iterations{scenario:prePubEventsDriverStart}": {
"type": "counter",
"contains": "default",
"values": {
"count": 1,
"rate": 0.0028933688751379555
},
"thresholds": {
"count>0": {
"ok": true
}
}
},
"http_req_duration{scenario:prePubEventsDriverStart}": {
"type": "trend",
"contains": "time",
"values": {
"avg": 0,
"min": 0,
"med": 0,
"max": 0,
"p(90)": 0,
"p(95)": 0
},
"thresholds": {
"avg <= 45": {
"ok": true
},
"med <= 45": {
"ok": true
},
"p(90)<=45": {
"ok": true
},
"p(95)<=45": {
"ok": true
}
}
},
"http_req_connecting": {
"type": "trend",
"contains": "time",
"values": {
"p(95)": 0,
"avg": 0.0003791068253453424,
"min": 0,
"med": 0,
"max": 9.383985,
"p(90)": 0
}
},
"http_req_receiving": {
"values": {
"avg": 0.052778179752667034,
"min": 0.013265,
"med": 0.046778,
"max": 15.117281,
"p(90)": 0.067176,
"p(95)": 0.08071009999999995
},
"type": "trend",
"contains": "time"
},
"iteration_duration{scenario:join}": {
"type": "trend",
"contains": "time",
"values": {
"avg": 601.8235918496938,
"min": 11.453644,
"med": 606.0548980000001,
"max": 640.327367,
"p(90)": 608.9491829,
"p(95)": 610.87953835
},
"thresholds": {
"avg <= 610": {
"ok": true
},
"med <= 610": {
"ok": true
},
"p(90)<=615": {
"ok": true
},
"p(95)<=620": {
"ok": true
}
}
},
"iterations{scenario:cachUpdate}": {
"contains": "default",
"values": {
"count": 6001,
"rate": 17.363106619702872
},
"thresholds": {
"count>0": {
"ok": true
}
},
"type": "counter"
},
"iteration_duration{scenario:find}": {
"type": "trend",
"contains": "time",
"values": {
"avg": 602.7191305882533,
"min": 12.527132,
"med": 607.5824175,
"max": 659.325018,
"p(90)": 610.23609,
"p(95)": 611.26768395
},
"thresholds": {
"avg <= 610": {
"ok": true
},
"med <=610": {
"ok": true
},
"p(90)<=615": {
"ok": true
},
"p(95)<=620": {
"ok": true
}
}
},
"http_req_waiting{scenario:cachUpdate}": {
"type": "trend",
"contains": "time",
"values": {
"p(95)": 0,
"avg": 0,
"min": 0,
"med": 0,
"max": 0,
"p(90)": 0
},
"thresholds": {
"avg <= 3": {
"ok": true
},
"med <= 1": {
"ok": true
},
"p(90)<=2": {
"ok": true
},
"p(95)<=2": {
"ok": true
}
}
},
"http_req_blocked{scenario:eventsDriverStop}": {
"type": "trend",
"contains": "time",
"values": {
"min": 0,
"med": 0,
"max": 0,
"p(90)": 0,
"p(95)": 0,
"avg": 0
},
"thresholds": {
"avg <= 1": {
"ok": true
},
"med <= 1": {
"ok": true
},
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
}
}
},
"http_req_blocked{scenario:eventsDriverStart}": {
"type": "trend",
"contains": "time",
"values": {
"p(90)": 0,
"p(95)": 0,
"avg": 0,
"min": 0,
"med": 0,
"max": 0
},
"thresholds": {
"avg <= 1": {
"ok": true
},
"med <= 1": {
"ok": true
},
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
}
}
},
"http_req_receiving{scenario:eventsDriverStop}": {
"thresholds": {
"avg <= 1": {
"ok": true
},
"med <= 1": {
"ok": true
},
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
}
},
"type": "trend",
"contains": "time",
"values": {
"avg": 0,
"min": 0,
"med": 0,
"max": 0,
"p(90)": 0,
"p(95)": 0
}
},
"iteration_duration{scenario:prePubEventsDriverStart}": {
"thresholds": {
"max>=0": {
"ok": true
}
},
"type": "trend",
"contains": "time",
"values": {
"min": 41518.762811,
"med": 41518.762811,
"max": 41518.762811,
"p(90)": 41518.762811,
"p(95)": 41518.762811,
"avg": 41518.762811
}
},
"http_req_sending{scenario:cachUpdate}": {
"type": "trend",
"contains": "time",
"values": {
"avg": 0,
"min": 0,
"med": 0,
"max": 0,
"p(90)": 0,
"p(95)": 0
},
"thresholds": {
"avg <= 1": {
"ok": true
},
"med <= 4": {
"ok": true
},
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
}
}
},
"http_req_tls_handshaking": {
"type": "trend",
"contains": "time",
"values": {
"p(90)": 0,
"p(95)": 0,
"avg": 0,
"min": 0,
"med": 0,
"max": 0
}
},
"iterations": {
"type": "counter",
"contains": "default",
"values": {
"rate": 373.091236342414,
"count": 128947
}
},
"http_req_blocked{scenario:join}": {
"type": "trend",
"contains": "time",
"values": {
"p(95)": 0.006021,
"avg": 0.004797885249958743,
"min": 0.001803,
"med": 0.004248,
"max": 0.44682,
"p(90)": 0.0055209
},
"thresholds": {
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
},
"avg <= 1": {
"ok": true
},
"med <= 1": {
"ok": true
}
}
},
"http_req_sending{scenario:eventsDriverStop}": {
"thresholds": {
"avg <= 1": {
"ok": true
},
"med <= 1": {
"ok": true
},
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
}
},
"type": "trend",
"contains": "time",
"values": {
"p(95)": 0,
"avg": 0,
"min": 0,
"med": 0,
"max": 0,
"p(90)": 0
}
},
"http_req_duration{scenario:con}": {
"thresholds": {
"avg <= 5": {
"ok": true
},
"med <= 3": {
"ok": true
},
"p(90)<=3.5": {
"ok": true
},
"p(95)<=6.5": {
"ok": true
}
},
"type": "trend",
"contains": "time",
"values": {
"avg": 1.585340762963596,
"min": 0.584217,
"med": 1.014234,
"max": 99.649419,
"p(90)": 2.5346404,
"p(95)": 4.764954199999999
}
},
"http_req_receiving{scenario:con}": {
"type": "trend",
"contains": "time",
"values": {
"avg": 0.05356539157839449,
"min": 0.014327,
"med": 0.048171,
"max": 15.117281,
"p(90)": 0.06851080000000001,
"p(95)": 0.08132260000000001
},
"thresholds": {
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
},
"avg <= 1": {
"ok": true
},
"med <= 1": {
"ok": true
}
}
},
"http_req_blocked{scenario:cachUpdate}": {
"type": "trend",
"contains": "time",
"values": {
"p(90)": 0,
"p(95)": 0,
"avg": 0,
"min": 0,
"med": 0,
"max": 0
},
"thresholds": {
"avg <= 1": {
"ok": true
},
"med <= 1": {
"ok": true
},
"p(90)<=1": {
"ok": true
},
"p(95)<=1": {
"ok": true
}
}
}
}
}
function:
CREATE OR REPLACE FUNCTION public.loaddt_get_metrics(_id integer)
RETURNS text
LANGUAGE plpgsql
AS $function$
declare
_row text;
_output text;
begin
for _row in (select json_object_keys(l.info->'metrics') from loaddt l where l.id = _id) loop -- Правильный получение ключей
_output = concat((select sub.metric->'values'->>'p(90)' as p90, sub.metric->'values'->>'p(95)' as p95, sub.metric->'values'->>'med' as med, sub.metric->'values'->>'avg'
as avg), '|') from (select (l.info->'metrics'->>_row)::json as metric from loaddt l ) sub;
end loop;
return _output;
end;
$function$
;
Error:
SQL Error [42601]: ERROR: subquery must return only one column
Where: PL/pgSQL function loaddt_get_metrics(integer) line 8 at assignment
OR
SQL Error [42601]: ERROR: subquery must return only one column
Where: PL/pgSQL function loaddt_get_metrics(integer) line 7 at assignment
CodePudding user response：
It's not clear from your question what are you trying to achieve, but maybe simple sql would suffice.
Assume you have table loaddt:
create table loaddt(
id int primary key,
info json
);
Then you can replace your loop with a query:
create or replace function loaddt_get_metrics(_id integer)
returns table(key text, output text)
language sql
as $function$
select
js.key as metric,
concat_ws(
'|',
js.value->'values'->>'p(90)',
js.value->'values'->>'p(95)',
js.value->'values'->>'med',
js.value->'values'->>'avg') as output
from loaddt as t, json_each(t.info->'metrics') as js
where t.id = _id
$function$
;