Database
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:
1
CREATE TABLE "patient" (
2
id text PRIMARY KEY, // id of resource
3
txid bigint not null, // version id and logical transaction id
4
ts timestamptz DEFAULT NOW(), // last updated time
5
resource_type text, // resource type
6
status resource_status not null, // resource status
7
resource jsonb not null // resource body
8
);
Copied!
You use the DB Console to explore the database:
1
select * from "entity" limit 10
Copied!
As you can see, resources are stored as JSONB documents in the resource column.
Resources are stored in Aidbox JSON format, which is more friendly for storage, and converted into FHIR in REST API on the fly!
You can access attributes of resources using PostgreSQL JSON functions:
1
SELECT
2
resource#>>'{name,0,famly}' as last_name,
3
resource#>>'{name,0,given,0}' as first_name
4
FROM "patient"
5
LIMIT 10
Copied!

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 modified 1mo ago
Copy link