-
Docker and Docker-Compose
-
Pull PostgreSQL 11 image for master class with JsQuery extensions
docker pull aidbox/db:11.1.0-3-g7a1dab6- Install
psqlor any sql terminal\editor
- What's JSONB?
- Why JSONB?
- ORM Impedance
- DDD Aggregates & Document databases
- Nested Data Structures
- Recursive Data Structures
- Denormalization on steroids
- What are tread-offs?
- how fast access to jsonb fields
- how big is jsonb in database
- How to store? Schema?
- How to update? * How to search in jsonb?
- How to index jsonb?
- Why do we need JSONb
- Relational-document database
- Document database pro and contra
- Open World Assumption
Key points:
- Flexible open schema (you do not need to add new columns) - variability (validation????)
- Nested hierarchiecal document - Aggregate from DDD (compare with relational) - denormalization on steroid
- Install PostgreSQL11
- Load initial dataset
- JSONB basic operators
- Get data from Github
- Work with jsonb data
See: ./getting-started.md
Disadvantages
- Access attributes - 10-30%
- Volume
- Lake of data types (only few)
Advantages
- Recursive datatypes
- Open schema
- Nested data structure
- 20-30% slower then column
- About twice faster then jsonb
- (unexpected) faster then composite types
Patient from https://www.hl7.org/fhir/patient-example.json
Size is 3.6 K (fit page) keys ~ 30 % in bytes
The smaller json and more numbers - the worse
jsonb_extract_pathandjsonb_extract_path_text- Constructors
jsonb_build_objectjsonb_build_array jsonb_strip_nullsrow_to_json||and-jsonb_set/jsonb_insert
See: ./crud.md
create index if not exists valueset_resource_url on valueset ((resource#>>'{url}')) ;create index if not exists concept_resource__gin_tgrm
on concept using gin ((resource::text) gin_trgm_ops) ;
update codesystem set resource = resource || jsonb_build_object('deprecated', true)
where resource->>'module' = 'fhir-3.3.0';- Constraints
- JSONB aggregate
- JSON as compositional
You do not need ORM!