About iterate nested array json

Help JSON
1

i have table with json filed which contain array like [“label_1”, “label_2”]. After use json_agg i get object as nested list [[“label_1”, “label_2”], [“label_1”, “label_2”], [“label_1”, “label_2”]]. Then i create function where i pass this object and i should get json as array with unique items. Same func ARRAY_AGG(DESC json) from postgresql. I need optimize my function,

Function:
CREATE
OR
REPLACE FUNCTION unique_items_by_nested_array(a ARRAY(JSON) NULL) RETURNS JSON AS
DECLARE
ret JSON = ‘[]’;
BEGIN
FOR i IN 0 … LENGTH(a) - 1 LOOP
FOR j IN 0 … LENGTH(JSON_TO_ARRAY(a[i])) - 1 LOOP
IF JSON_ARRAY_CONTAINS_STRING(ret, JSON_TO_ARRAY(a[i])[j]) = 0 THEN
ret = JSON_ARRAY_PUSH_STRING(ret, JSON_TO_ARRAY(a[i])[j]);
END IF;
END LOOP;
END LOOP;
RETURN ret;
END;

select unique_items_by_nested_array(JSON_TO_ARRAY(json_agg(label))) from test_table;

Сan I somehow access the nested list without unnecessary conversion to json_to_array, but just a[i][j]?? Are there any other options to convert a json array of unique values ​​from the entire table of json fields in a different way??

2

Check out my reply to your related post. Does that answer your question?

1 Like