npm package: @nsshunt/stsdatamanagement
Description: STS Data Management Modules, Utilities and Services
Author: STS
Latest version: 1.18.186

# stsdatamanagement
STS Data Management Modules, Utilities and Services

To create a database for testing use the following:

docker run --name postgres-5432 -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d --restart unless-stopped postgres \
-c max_connections=500 \
-c shared_buffers=2GB \
-c effective_cache_size=6GB \
-c maintenance_work_mem=512MB \
-c checkpoint_completion_target=0.9 \
-c wal_buffers=16MB \
-c default_statistics_target=100 \
-c random_page_cost=1.1 \
-c effective_io_concurrency=200 \
-c work_mem=1048kB \
-c min_wal_size=2GB \
-c max_wal_size=8GB \
-c max_worker_processes=6 \
-c max_parallel_workers_per_gather=3 \
-c max_parallel_workers=6 \
-c max_parallel_maintenance_workers=3



/* Samples Data
{"id":"_b8b74a73_3275_4094_93b1_64a533c5924c_0_0fhir02_0","_resourceType":"Person","name":[{"family":"Mccall","given":"Shane","use":"usual"}],"text":{"div":"Updated record 0","status":"generated"}}
*/

select count(*) from stsresource;

/* Create General Search Indexes */

/* GIN Index */
--drop index idx_stsresource_gin;
CREATE INDEX idx_stsresource_gin ON stsresource USING GIN (resdesc_jsonb);

/* btree indexes */
--drop index idx_stsresource_id;
--We don't use where here becuase all FHIR resources will have an id field
CREATE INDEX idx_stsresource_id ON stsresource ((resdesc_jsonb->>'id'));

-- Create Indexes for FHIR resources

--DROP INDEX idx_stsresource_given;
CREATE INDEX idx_stsresource_given 
ON stsresource ((resdesc_jsonb->'name'->0->>'given'))
WHERE resdesc_jsonb->>'_resourceType' = 'Person';

--drop index idx_stsresource_family;
CREATE INDEX idx_stsresource_family ON stsresource ((resdesc_jsonb->'name'->0->>'family'))
WHERE resdesc_jsonb->>'_resourceType' = 'Person';

--drop index idx_stsresource_text_div_01;
CREATE INDEX idx_stsresource_text_div_01
ON stsresource ((resdesc_jsonb->'text'->>'div'))
WHERE resdesc_jsonb->>'_resourceType' = 'Person';

/* btree indexes - alternate method - for queries, quotes will be need '"<search string>"' */
--drop index idx_stsresource_text_div_02;
CREATE INDEX idx_stsresource_text_div_02
ON stsresource ((resdesc_jsonb#>'{text,div}'))
WHERE (resdesc_jsonb#>'{_resourceType}')::text = '"Person"';

/* Queries using our indexes */

/* Get records using the GIN index */
SELECT *
FROM stsresource
WHERE resdesc_jsonb @> '{"_resourceType": "Person"}'
  AND resdesc_jsonb @> '{"name": [{"family": "Adams"}]}'
LIMIT 20

SELECT oid, resdesc_jsonb#>'{text,div}' AS text_div
FROM stsresource
WHERE resdesc_jsonb @> '{"_resourceType": "Person"}'
  AND resdesc_jsonb @> '{"text": {"div": "New Record 100"}}';

/* Get record using the BTREE index given */
SELECT resdesc_jsonb->'id' as id,
	resdesc_jsonb->'name'->0->>'given' as given,
	resdesc_jsonb->'name'->0->>'family' as family,
	resdesc_jsonb->'name' as name
FROM stsresource 
WHERE resdesc_jsonb->>'_resourceType' = 'Person'
  AND resdesc_jsonb->'name'->0->>'given' >= 'y' and resdesc_jsonb->'name'->0->>'given' < 'yzzzzzzzzzzzzz'
  AND validto is null
ORDER BY family, given, id
LIMIT 20;

/* Get record using the BTREE index 01 */
SELECT oid, resdesc_jsonb#>'{text,div}' AS text_div
FROM stsresource
WHERE resdesc_jsonb->>'_resourceType' = 'Person'
  AND resdesc_jsonb->'text'->>'div' = 'New Record 200';

/* Get record using the BTREE index 02 */
SELECT oid, resdesc_jsonb#>'{text,div}' AS text_div
FROM stsresource
WHERE (resdesc_jsonb#>'{_resourceType}')::text = '"Person"'
  AND resdesc_jsonb#>'{text,div}' = '"New Record 400"';
  
/* Query using id index */
SELECT oid, resdesc_jsonb->>'id' as id, * 
FROM stsresource 
WHERE resdesc_jsonb->>'id' >= '_' and resdesc_jsonb->>'id' < '_zzzzzzzzzzzz'
ORDER BY resdesc_jsonb->>'id'
LIMIT 20;

/* Query using given index */
SELECT distinct given, family
FROM (
	SELECT resdesc_jsonb->'name'->0->>'given' as given, resdesc_jsonb->'name'->0->>'family' as family
  	FROM stsresource 
  	WHERE resdesc_jsonb->>'_resourceType' = 'Person'
    	AND validto is null
    	AND resdesc_jsonb->'name'->0->>'given' >= 'a'
    	AND resdesc_jsonb->'name'->0->>'given' < 'azzzzzzz'
	)
ORDER BY given DESC
LIMIT 20;

/* Query using family indexes */
SELECT distinct resdesc_jsonb->'name'->0->>'given' as given, resdesc_jsonb->'name'->0->>'family' as family
FROM stsresource 
WHERE resdesc_jsonb->>'_resourceType' = 'Person'
	AND validto is null
	AND resdesc_jsonb->'name'->0->>'family' >= 'y'
	AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz'
ORDER BY given DESC	
LIMIT 20
		  
/* Query using family and given indexes */
SELECT distinct givenzz, familyzz
FROM (
	SELECT resdesc_jsonb->'name'->0->>'given' as givenzz, resdesc_jsonb->'name'->0->>'family' as familyzz
	FROM stsresource 
	WHERE resdesc_jsonb->>'_resourceType' = 'Person'
  		AND validto is null
		AND resdesc_jsonb->'name'->0->>'given' >= 'a'
		AND resdesc_jsonb->'name'->0->>'given' < 'azzzzzzz'
  		AND resdesc_jsonb->'name'->0->>'family' >= 'y'
  		AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz'
	)
ORDER BY givenzz DESC
LIMIT 20;

/* Query using given indexes */
select count(*) from (
	SELECT distinct resdesc_jsonb->'name'->0->>'given' as given
	FROM stsresource 
	WHERE validto is null
		AND resdesc_jsonb->>'_resourceType' = 'Person'
		AND resdesc_jsonb->'name'->0->>'given' >= 'al'
		AND resdesc_jsonb->'name'->0->>'given' < 'alzzzzzzz'
)


SELECT distinct resdesc_jsonb->'name'->0->>'given', resdesc_jsonb->'name'->0->>'family'
FROM stsresource
where validto is null
AND resdesc_jsonb->>'_resourceType' = 'Person'
AND resdesc_jsonb->'name'->0->>'given' >= 'a'
AND resdesc_jsonb->'name'->0->>'given' < 'azzzzzzz'
AND resdesc_jsonb->'name'->0->>'family' >= 's'
AND resdesc_jsonb->'name'->0->>'family' < 'szzzzzzz'
ORDER BY
	resdesc_jsonb->'name'->0->>'given' DESC,
	resdesc_jsonb->'name'->0->>'family' DESC
LIMIT 20;



SELECT distinct resdesc_jsonb->'name'->0->>'given' as givenzz, resdesc_jsonb->'name'->0->>'family' as familyzz
FROM stsresource
where validto is null
AND resdesc_jsonb->>'_resourceType' = 'Person'
AND resdesc_jsonb->'name'->0->>'given' >= 'a'
AND resdesc_jsonb->'name'->0->>'given' < 'azzzzzzz'
AND resdesc_jsonb->'name'->0->>'family' >= 's'
AND resdesc_jsonb->'name'->0->>'family' < 'szzzzzzz'
ORDER BY
	givenzz DESC,
	familyzz DESC
LIMIT 20;


SELECT distinct given as givenzz, family as familyzz
FROM stsresource
where validto is null
AND given >= 'a'
AND given < 'azzzzzzz'
AND family >= 'n'
AND family < 'nzzzzzzz'
ORDER BY
	givenzz DESC,
	familyzz DESC
LIMIT 20;





SELECT given, oid, *
FROM stsresource
where validto is null
and given >= 'a'
and given < 'azzzzzz'
ORDER BY given
LIMIT 20;





select count(*) from stsresource;


SELECT distinct givenzz, familyzz
FROM (
	SELECT resdesc_jsonb->'name'->0->>'given' as givenzz, resdesc_jsonb->'name'->0->>'family' as familyzz
	FROM stsresource 
	WHERE resdesc_jsonb->>'_resourceType' = 'Person'
  		AND validto is null
		AND resdesc_jsonb->'name'->0->>'family' >= 'y'
		AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz'	
		AND resdesc_jsonb->'name'->0->>'given' >= 'a'
		AND resdesc_jsonb->'name'->0->>'given' < 'azzzzzzz'
	)
ORDER BY givenzz DESC
LIMIT 20;




SELECT distinct givenzz, familyzz
FROM (
	select * from (
		SELECT resdesc_jsonb->'name'->0->>'given' as givenzz, resdesc_jsonb->'name'->0->>'family' as familyzz
		FROM stsresource 
		WHERE resdesc_jsonb->>'_resourceType' = 'Person'
	  		AND validto is null
			AND resdesc_jsonb->'name'->0->>'family' >= 'y'
			AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz'	
	)
	where givenzz >= 'a'
	  and givenzz < 'azzzzzzzzzzzz'
)
ORDER BY givenzz DESC
LIMIT 20;



SELECT *
FROM (
    SELECT resdesc_jsonb->'name'->0->>'given' AS givenzz, 
           resdesc_jsonb->'name'->0->>'family' AS familyzz
    FROM stsresource
    WHERE resdesc_jsonb->>'_resourceType' = 'Person'
      AND validto IS NULL
      AND resdesc_jsonb->'name'->0->>'family' >= 'y'
      AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz'
) subquery
WHERE givenzz >= 'a'
  AND givenzz < 'azzzzzzzzzzzz'
ORDER BY givenzz DESC
LIMIT 20;



BEGIN;

-- Step 1: Create and populate the temporary table
SELECT *
INTO TEMPORARY TABLE temp_table_name
FROM (
    SELECT resdesc_jsonb->'name'->0->>'given' AS givenzz, 
           resdesc_jsonb->'name'->0->>'family' AS familyzz
    FROM stsresource
    WHERE resdesc_jsonb->>'_resourceType' = 'Person'
      AND validto IS NULL
      AND resdesc_jsonb->'name'->0->>'family' >= 'y'
      AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz'
) subquery
WHERE givenzz >= 'a'
  AND givenzz < 'azzzzzzzzzzzz';

-- Step 2: Query the temporary table
SELECT DISTINCT givenzz, familyzz
FROM temp_table_name
ORDER BY givenzz DESC
LIMIT 20;

DROP TABLE temp_table_name;

COMMIT;



SELECT given, family
FROM (
	select * from (
		SELECT given, family
		FROM stsresource 
		WHERE 
	  		validto is null
			AND given >= 'a'
			AND given < 'azzzzzzz'
  	) subquery
	where family >= 'y'
		and family < 'yzzzzzzzzzzzz'
)
ORDER BY given DESC
LIMIT 20;



SELECT given, family
FROM (
	SELECT given, family
	FROM stsresource 
	WHERE 
  		validto is null
		AND given >= 'a'
		AND given < 'azzzzzzz'
  		AND family >= 'y'
  		AND family < 'yzzzzzzzzzzzz'
	)
ORDER BY given DESC
LIMIT 20;



WITH temp_table_name AS (
    SELECT resdesc_jsonb->'name'->0->>'given' AS givenzz, 
           resdesc_jsonb->'name'->0->>'family' AS familyzz,
		   oid
    FROM stsresource
    WHERE resdesc_jsonb->>'_resourceType' = 'Person'
      AND validto IS NULL
      AND resdesc_jsonb->'name'->0->>'family' >= 'y'
      AND resdesc_jsonb->'name'->0->>'family' < 'yzzzzzzzzzzzz'
),
tt2 AS (
	SELECT givenzz, familyzz, oid
	FROM temp_table_name
	WHERE givenzz >= 'a'
  		AND givenzz < 'azzzzzzzzzzzz'
)
select * from tt2
ORDER BY givenzz ASC
LIMIT 20;



-- select * from search_stsresource_given_family_v3('s', 'b');
-- drop function search_stsresource_given_family_v3;
CREATE OR REPLACE FUNCTION public.search_stsresource_given_family_v3(
	_given character varying,
	_family character varying)
	RETURNS TABLE (given text, family text, oid bigint)	
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
    ROWS 1000

AS $BODY$
DECLARE
	_resdesc_jsonb JSONB;
BEGIN
		return query

WITH temp_table_name AS (
    SELECT r.resdesc_jsonb->'name'->0->>'given' AS givenzz, 
           r.resdesc_jsonb->'name'->0->>'family' AS familyzz,
		   r.oid
    FROM stsresource r
    WHERE r.resdesc_jsonb->>'_resourceType' = 'Person'
      AND r.validto IS NULL
      AND r.resdesc_jsonb->'name'->0->>'family' >= _family
      AND r.resdesc_jsonb->'name'->0->>'family' < _family || 'zzzzzzzzzzzz'
),
tt2 AS (
	SELECT ttn.givenzz as given, ttn.familyzz as family, ttn.oid
	FROM temp_table_name ttn
	WHERE ttn.givenzz >= _given
  		AND ttn.givenzz < _given || 'zzzzzzzzzzzz'
)
select * from tt2
ORDER BY given ASC
LIMIT 20;

END;
$BODY$;

ALTER FUNCTION public.search_stsresource_given_family_v3(character varying, character varying)
    OWNER TO postgres;


-- select * from search_stsresource_given_family_v1('a', 'y');
-- drop function search_stsresource_given_family_v1;
CREATE OR REPLACE FUNCTION public.search_stsresource_given_family_v1(
	_given character varying,
	_family character varying)
	RETURNS TABLE (given character varying, family character varying, oid bigint)	
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
    ROWS 1000

AS $BODY$
DECLARE
	_resdesc_jsonb JSONB;
BEGIN
		return query

SELECT rt.given, rt.family, rt.oid
FROM (
	SELECT r.given, r.family, r.oid
	FROM stsresource r
	WHERE 
  		r.validto is null
		AND r.given >= _given
		AND r.given < _given || 'zzzzzzz'
  		AND r.family >= _family
  		AND r.family < _family || 'zzzzzzzzzzzz'
	) rt
ORDER BY rt.given DESC
LIMIT 20;

END;
$BODY$;

ALTER FUNCTION public.search_stsresource_given_family_v1(character varying, character varying)
    OWNER TO postgres;