JSON template ning tarkibidan ko`rsatilgan blockni olib tashlash

JSON template ning tarkibidan ko`rsatilgan blockni olib tashlash uchun quyidagicha ajoyib querydan foydalanish mumkin:





create or replace function remove_text(a varchar, t text,_first text='', _last text='', _position integer=0, _qavssoni integer=0 )
 returns text as
$BODY$
begin
select position(a in t) into _position;
select substring(t,0,_position) into _first;
select substring(t,_position+length(a)+1,length(t)) into _last;
select clear_qavs_block(_last, 0) into _last;
select _first||_last into t;
if(position(a in t)>0)
then return remove_text(a,t);
else
return t;
end if;
end;
$BODY$
LANGUAGE plpgsql VOLATILE;

create or replace function clear_qavs_block(a text, _qavscount integer)
returns text as
$body$
declare x varchar(1)=(select substring(a,1,1));
begin
select (case x when '{' then 1 when '}' then -1 else 0 end)+_qavscount into _qavscount;
select substring(a,2,length(a)-1) into a;
if(_qavscount>0) then return clear_qavs_block(a,_qavscount);
else
return substring(a,2,length(a));
end if;


end;
$body$
LANGUAGE plpgsql VOLATILE;

select remove_text('"status"','{"facetFilter":{"facetContentList":{"caseorign":{},"relatedContact":{},"status":{"0":{"id":"527","name":"New"}},"assignees":{"1":{"id":"41","name":"Allison Cayse"},"0":{"id":"7","name":"Doni Abdulxafizov"}},"priority":{},"department":{},"resolver":{},"reportby":{},"type":{},"relatedCrmAccount":{}},"facetCustomList":{},"facetSettingList":{"caseorign":{"trow":"2","tcell":"1"},"relatedContact":{"trow":"3","tcell":"1"},"status":{"trow":"1","tcell":"4"},"assignees":{"trow":"2","tcell":"2"},"priority":{"trow":"1","tcell":"3"},"department":{"trow":"2","tcell":"3"},"resolver":{"trow":"2","tcell":"4"},"reportby":{"trow":"1","tcell":"1"},"type":{"trow":"1","tcell":"2"},"facetDatePeriod":{"trow":"3","tcell":"3"},"relatedCrmAccount":{"trow":"3","tcell":"2"}}}}')


ORIGINAL--{"facetFilter":{"facetContentList":{"caseorign":{},"relatedContact":{},"status":{"0":{"id":"527","name":"New"}},"assignees":{"1":{"id":"41","name":"Allison Cayse"},"0":{"id":"7","name":"Doni Abdulxafizov"}},"priority":{},"department":{},"resolver":{},"reportby":{},"type":{},"relatedCrmAccount":{}},"facetCustomList":{},"facetSettingList":{"caseorign":{"trow":"2","tcell":"1"},"relatedContact":{"trow":"3","tcell":"1"},"status":{"trow":"1","tcell":"4"},"assignees":{"trow":"2","tcell":"2"},"priority":{"trow":"1","tcell":"3"},"department":{"trow":"2","tcell":"3"},"resolver":{"trow":"2","tcell":"4"},"reportby":{"trow":"1","tcell":"1"},"type":{"trow":"1","tcell":"2"},"facetDatePeriod":{"trow":"3","tcell":"3"},"relatedCrmAccount":{"trow":"3","tcell":"2"}}}}
STATUS BLOCKi O`CHIRILGAN--{"facetFilter":{"facetContentList":{"caseorign":{},"relatedContact":{},"assignees":{"1":{"id":"41","name":"Allison Cayse"},"0":{"id":"7","name":"Doni Abdulxafizov"}},"priority":{},"department":{},"resolver":{},"reportby":{},"type":{},"relatedCrmAccount":{}},"facetCustomList":{},"facetSettingList":{"caseorign":{"trow":"2","tcell":"1"},"relatedContact":{"trow":"3","tcell":"1"},"assignees":{"trow":"2","tcell":"2"},"priority":{"trow":"1","tcell":"3"},"department":{"trow":"2","tcell":"3"},"resolver":{"trow":"2","tcell":"4"},"reportby":{"trow":"1","tcell":"1"},"type":{"trow":"1","tcell":"2"},"facetDatePeriod":{"trow":"3","tcell":"3"},"relatedCrmAccount":{"trow":"3","tcell":"2"}}}}
DEPARTMENT BLOCKi O`CHIRILGAN--{"facetFilter":{"facetContentList":{"caseorign":{},"relatedContact":{},"status":{"0":{"id":"527","name":"New"}},"assignees":{"1":{"id":"41","name":"Allison Cayse"},"0":{"id":"7","name":"Doni Abdulxafizov"}},"priority":{},"resolver":{},"reportby":{},"type":{},"relatedCrmAccount":{}},"facetCustomList":{},"facetSettingList":{"caseorign":{"trow":"2","tcell":"1"},"relatedContact":{"trow":"3","tcell":"1"},"status":{"trow":"1","tcell":"4"},"assignees":{"trow":"2","tcell":"2"},"priority":{"trow":"1","tcell":"3"},row":"2","tcell":"3"},"resolver":{"trow":"2","tcell":"4"},"reportby":{"trow":"1","tcell":"1"},"type":{"trow":"1","tcell":"2"},"facetDatePeriod":{"trow":"3","tcell":"3"},"relatedCrmAccount":{"trow":"3","tcell":"2"}}}}