Database schema
Aidbox database schema
All resource types in Aidbox are stored in different tables, named with a lowercased resource type name. All these tables have a similar schema:
CREATE TABLE "patient" (
id text PRIMARY KEY, -- id of resource
txid bigint not null, -- version id and logical transaction id
ts timestamptz DEFAULT NOW(), -- last updated time
resource_type text, -- resource type
status resource_status not null, -- resource status
resource jsonb not null -- resource body
);
You use the DB Console to explore the database:
select * from "entity" limit 10
As you can see, resources are stored as JSONB documents in the resource column.
You can access attributes of resources using PostgreSQL JSON functions:
SELECT
resource#>>'{name,0,famly}' as last_name,
resource#>>'{name,0,given,0}' as first_name
FROM "patient"
LIMIT 10
Custom Queries
You can define and expose over REST API sophisticated queries in SQL on FHIR data using Custom Queries.
Tutorials
Check out our video tutorial about SQL on FHIR in PostgreSQL:
Last updated
Was this helpful?