Week 06 ‐ Devlog
Wrapping up the Performance and JSONB milestone, we will need to add new metadata and setup JSONB columns.
Major tasks:
- ALTER TABLE plants ADD COLUMN metadata JSONB DEFAULT '{}'
- Update one hydro plant with nutrient mix and reservoir specs
- Update one outdoor plant with soil amendments and sun exposure
- Query with ->> to extract a field as text
- Query with @> to check array or object containment
- CREATE INDEX USING GIN on metadata column
- Verify GIN index in pg_indexes
- Evidence: two plants with different JSON structures, both query results
Add the column for metadata to plants:
ALTER TABLE public.plants
ADD COLUMN metadata JSONB DEFAULT '{}';
Update the hydro plant, let's use tomatoes since I had recently grown some in the last couple years and they did really well:
UPDATE public.plants
SET metadata = '{
"grow_medium": "coco coir",
"nutrient_line": "General Hydroponics Flora Series",
"reservoir_size_gal": 5,
"ec_target": 1.4,
"ph_target": 5.9
}'::jsonb
WHERE id = 'bbbbbbbb-0000-0000-0000-000000000001';
And to update the outdoor plant, basil. Currently, I have four different basil plants and they're all growing so differently. I will need to use metadata to track the descriptions on these later, especially different types from Thai to Genovese. For basic purposes, we will just add common amendments and things they like (like Perlite!) Sometimes you transfer hydroponic plants to soil, so that is something we will use JSONB in the later as well.
SELECT common_name, metadata->>'grow_medium' AS grow_medium
FROM public.plants
WHERE metadata->>'grow_medium' IS NOT NULL;
SELECT common_name, metadata->'amendments' AS amendments
FROM public.plants
WHERE metadata @> '{"sun_exposure": "full sun"}';
CREATE INDEX idx_plants_metadata ON public.plants USING GIN (metadata);
Verify the query with all operators and GIN index is solid:
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'plants'
AND indexname = 'idx_plants_metadata';
Tomato will return with coco coir as the grow medium, Basil will have compost and perlite as preferred amendments in an array, and the jagged data demo is working as intended. The metadata definitions are defined as "CREATE INDEX idx_plants_metadata ON public.plants USING gin (metadata)" and thus concluding the Performance and JSONB milestone.
- JSONB let two plants store completely different metadata in one column. nutrient and reservoir specs for the hydro plant, soil amendments and sun exposure for the outdoor plant.
- GIN index deployed on
metadata; both->>extraction and@>containment queries returned correct results.
metadata tomato
metadata basil
metadata definitions
