Created
August 16, 2018 07:34
-
-
Save ppKrauss/679cea825002076c8697e734763076b9 to your computer and use it in GitHub Desktop.
Tool-kit for JSONb key-number objects
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * object_summable and "Merge sum" functions are for JSONb key-number objects, | |
| * most usually key-integer for counting, like json-bags. | |
| * They are "key counters", so, to merge two keys, the intValues must be added. | |
| * Change the core of jsonb_merge_sum(jsonb,jsonb) to the correct datatype. | |
| * The JSON "number" is equivalent to the SQL's ::numeric. | |
| * Any invalid or empty JSONb object will be represented as SQL NULL. | |
| */ | |
| CREATE or replace FUNCTION jsonb_is_summable(jsonb) RETURNS boolean AS $f$ | |
| SELECT not($1 IS NULL OR jsonb_typeof($1)!='object' OR $1='{}'::jsonb) | |
| AND (SELECT bool_and(jsonb_typeof(value)='number') FROM jsonb_each($1)) | |
| -- for bigint use jsonb_each_text() with regex, value ~ '^\d+$'. | |
| $f$ language SQL IMMUTABLE; | |
| CREATE or replace FUNCTION jsonb_merge_sum( jsonb, jsonb ) RETURNS jsonb AS $f$ | |
| SELECT CASE | |
| WHEN emp1 AND emp2 THEN NULL | |
| WHEN emp2 THEN $1 | |
| WHEN emp1 THEN $2 | |
| ELSE $1 || ( | |
| -- CHANGE this core operation to enforce ::number or ::float or ::bigint | |
| SELECT jsonb_object_agg( key, value::int + COALESCE(($1->>key)::int,0) ) | |
| FROM jsonb_each_text($2) | |
| ) END | |
| FROM ( | |
| SELECT $1 IS NULL OR jsonb_typeof($1)!='object' OR $1='{}'::jsonb emp1, | |
| $2 IS NULL OR jsonb_typeof($2)!='object' OR $2='{}'::jsonb emp2 | |
| ) t | |
| $f$ language SQL IMMUTABLE; | |
| CREATE or replace FUNCTION jsonb_merge_sum( jsonb[] ) RETURNS jsonb AS $f$ | |
| DECLARE | |
| x JSONb; | |
| j JSONb; | |
| BEGIN | |
| IF $1 IS NULL OR array_length($1,1)=0 THEN | |
| RETURN NULL; | |
| ELSEIF array_length($1,1)=1 THEN | |
| RETURN $1[1]; | |
| END IF; | |
| x := $1[1]; | |
| FOREACH j IN ARRAY $1[2:] LOOP | |
| x:= jsonb_merge_sum(x,j); | |
| END LOOP; | |
| RETURN x; | |
| END | |
| $f$ LANGUAGE plpgsql IMMUTABLE; | |
| CREATE or replace FUNCTION jsonb_merge_sum( jsonb[], jsonb[] ) RETURNS jsonb[] AS $f$ | |
| SELECT CASE | |
| WHEN $2 IS NULL THEN $1 | |
| WHEN $1 IS NULL THEN $2 | |
| ELSE ( | |
| SELECT array_agg( jsonb_merge_sum(j1,j2) ) | |
| FROM ( | |
| SELECT unnest(a) j1, unnest(b) j2 | |
| FROM array_fillto_duo($1,$2) t(a,b) | |
| ) t | |
| ) END | |
| $f$ language SQL IMMUTABLE; | |
| DROP AGGREGATE IF EXISTS jsonb_agg_mergesum(jsonb); | |
| CREATE AGGREGATE jsonb_agg_mergesum(jsonb) ( -- important! | |
| SFUNC=jsonb_merge_sum, | |
| STYPE=jsonb, | |
| INITCOND=NULL | |
| ); | |
| /* | |
| under review for use with initcond=NULL | |
| DROP AGGREGATE IF EXISTS jsonb_agg_mergesum(jsonb[]); | |
| CREATE AGGREGATE jsonb_agg_mergesum(jsonb[]) ( -- low use | |
| SFUNC=jsonb_merge_sum, | |
| STYPE=jsonb[], | |
| INITCOND='{}' -- test with null | |
| ); | |
| */ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment