badq.sql

select distinct 
  id
  , resource_type            

  -- Resource 
  , contained_practitioner_id
  , contained_practitioner_resourceType
  -- Name
  , contained_practitioner_lname
  , contained_practitioner_fname
  , contained_practitioner_mname
  , contained_practitioner_suffix
  -- Address
  , contained_practitioner_address_street
  , contained_practitioner_address_street_2
  , contained_practitioner_address_street_3

  , contained_practitioner_address_city 
  , contained_practitioner_address_state 
  , contained_practitioner_address_postalcode 
  , contained_practitioner_address_country 
  , contained_practitioner_address_use
  , contained_practitioner_identifier_value
  , contained_practitioner_identifier_system
  , txid
  , current_timestamp
  from ( 

    SELECT 
      src.id
      , src.resource_type    

      --Resource specific fields
      , jsonb_array_elements(resource->'contained')#>>'{id}' as contained_practitioner_id
      , jsonb_array_elements(resource->'contained')#>>'{resourceType}' as contained_practitioner_resourceType

      -- Name
      , UPPER(unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{name,0,family}') as contained_practitioner_lname
      , UPPER(unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{name,0,given,0}') as contained_practitioner_fname
      , UPPER(unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{name,0,given,1}') as contained_practitioner_mname
      , UPPER(unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{name,0,suffix,0}') as contained_practitioner_suffix
        

      -- address		
      , UPPER(unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{address, 0, line, 0}') as contained_practitioner_address_street	  		
      , UPPER(unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{address, 0, line, 1}')  as contained_practitioner_address_street_2
      , UPPER(unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{address, 0, line, 2}')  as contained_practitioner_address_street_3
      , UPPER(unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{address, 0, city}') as contained_practitioner_address_city
      , UPPER(unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{address, 0, state}')  as contained_practitioner_address_state
      , unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{address, 0, postalCode}'  as contained_practitioner_address_postalcode
      , UPPER(unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{address, 0, country}')  as contained_practitioner_address_country
      , UPPER(unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]'))#>>'{address, 0, use}')  as contained_practitioner_address_use
        

      -- Identifier
      , jsonb_array_elements(resource->'contained')#>>'{identifier, 0, value}' as contained_practitioner_identifier_value            
      , jsonb_array_elements(resource->'contained')#>>'{identifier, 0, system}' as contained_practitioner_identifier_system
        
        

      --meta
      , src.txid             

      FROM (-- encounter_contained_practitioner
            SELECT src.* FROM 
                           public.encounter src	
             where src.resource->'contained' is not null
               and knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]') is not null 
               and txid > (select max(txid)
                             from (
                               select max(txid) txid from assembly.encounter_contained_practitioner
                                union select 0 as txid
                             ) max_txid
               )  
             ORDER BY src.txid asc              
             limit resource_limit                    
      ) src  

 ) _