Postgres JSON Querying

 
with problems_with_mapping as ( 
    SELECT 
		pregnancies.id as pregnancy_id, 
		pregnancies.patient_id, 
		arr.position as index, 
		arr.item_object->>'snomed_icd10_mapping_id' as mapping_id, 
		arr.item_object->'ehr_data'->>'problem' as problem,
		arr.item_object->'ehr_data'->>'isActive' as isActive, 
		arr.item_object->'ehr_data'->>'isDeleted' as isDeleted, 
		arr.item_object->>'snomed_code' as snomed_code, 
		arr.item_object->>'name' as name
    from pregnancies, jsonb_array_elements(document->'problems') with ordinality arr(item_object, position)
    where arr.item_object->>'snomed_icd10_mapping_id' is not null
)
 
SELECT 
	patients.first_name, 
	patients.last_name, 
	problems_with_mapping.*, 
	snomed_icd10_mappings.* 
from problems_with_mapping
left join snomed_icd10_mappings on snomed_icd10_mappings.id::varchar(255) = problems_with_mapping.mapping_id::varchar(255)
left join patients on patients.id = problems_with_mapping.patient_id
where snomed_icd10_mappings.id is null