Getting Started: SQL Basics

-- truncate patient;
-- truncate observation;

POST /fhir/Patient/$load
Accept: text/yaml
Content-Type: text/yaml

source: 'https://storage.googleapis.com/aidbox-public/synthea/100/Patient.ndjson.gz'

{total: 124}

POST /fhir/Observation/$load
Accept: text/yaml
Content-Type: text/yaml

source: 'https://storage.googleapis.com/aidbox-public/synthea/100/Observation.ndjson.gz'

{total: 20382}

select count(*) from patient

select resource#>>'{gender}' as gender, count(*)
from patient
group by 1

select id
     , resource#>>'{name,0,family}' as family
     , resource#>>'{name,0,given,0}' as given
     , resource#>>'{birthDate}' as birthDate
     , extract(year from age(now(), (resource#>>'{birthDate}')::date)) as age
from patient
order by 5 desc
limit 5

select id, resource#>>'{subject, id}' as patient_id
from observation
limit 5

select extract(year from age(now(), (p.resource#>>'{birthDate}')::date)) as "Patient age"
     , p.resource#>>'{gender}' as "Patient gender"
     , o.resource#>>'{code,text}' as "Observation code"
     , concat(o.resource#>>'{value,Quantity,value}', ' ', o.resource#>>'{value,Quantity,unit}') as "Observation value"
from observation as o
join patient as p on p.id = o.resource#>>'{subject, id}'
limit 10

select relname as table_name
     , n_live_tup as count
from pg_stat_all_tables
where schemaname = 'public'
order by count desc
limit 10

SELECT nspname || '.' || relname AS relation, pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 10;