ups.sql

WITH
maxtx AS (
    select max(txid) as mtx
      from (
        select max(txid) txid from assembly.encounter_contained_practitioner
         union
        select 0 as txid
      ) _
),
src AS (
  SELECT * FROM public.encounter src
  where resource->'contained' is not null
    and knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]') is not null 
    and txid > (select mtx from maxtx limit 1)
  ORDER BY txid asc
  limit 1000
), pract AS (
  SELECT
    id, txid, resource_type,
    unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]')) as res
    FROM  src
), flat_pract AS (
    SELECT
      id
      , resource_type
      , res->>'id' as contained_practitioner_id
      , res->>'resourceType' as contained_practitioner_resourceType
      , UPPER(res#>>'{name,0,family}') as contained_practitioner_lname
      , UPPER(res#>>'{name,0,given,0}') as contained_practitioner_fname
      , UPPER(res#>>'{name,0,given,1}') as contained_practitioner_mname
      , UPPER(res#>>'{name,0,suffix,0}') as contained_practitioner_suffix
      , UPPER(res#>>'{address, 0, line, 0}') as contained_practitioner_address_street	  		
      , UPPER(res#>>'{address, 0, line, 1}')  as contained_practitioner_address_street_2
      , UPPER(res#>>'{address, 0, line, 2}')  as contained_practitioner_address_street_3
      , UPPER(res#>>'{address, 0, city}') as contained_practitioner_address_city
      , UPPER(res#>>'{address, 0, state}')  as contained_practitioner_address_state
      , res#>>'{address, 0, postalCode}'  as contained_practitioner_address_postalcode
      , UPPER(res#>>'{address, 0, country}')  as contained_practitioner_address_country
      , UPPER(res#>>'{address, 0, use}')  as contained_practitioner_address_use
      , res#>>'{identifier, 0, value}' as contained_practitioner_identifier_value            
      , res#>>'{identifier, 0, system}' as contained_practitioner_identifier_system
      , txid
      from pract
)
select distinct
  id
  , resource_type
  , contained_practitioner_id
  , contained_practitioner_resourceType
  , contained_practitioner_lname
  , contained_practitioner_fname
  , contained_practitioner_mname
  , contained_practitioner_suffix
  , 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 flat_pract

v10.sql

SELECT count(*) FROM public.encounter where (resource->'contained') @> '[{"resourceType": "Practitioner"}]'

v2.sql

WITH
maxtx AS (
    select max(txid) as mtx
      from (
        select max(txid) txid from assembly.encounter_contained_practitioner
         union
        select 0 as txid
      ) _
),
src AS (
  SELECT * FROM public.encounter src
  where resource->'contained' is not null
    and knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]') is not null 
    and txid > (select mtx from maxtx limit 1)
  ORDER BY txid asc
  limit 1000
), pract AS (
  SELECT
    id, txid, resource_type,
    unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]')) as res
    FROM  src
), flat_pract AS (
    SELECT
      id
      , resource_type
      , res->>'id' as contained_practitioner_id
      , res->>'resourceType' as contained_practitioner_resourceType
      , UPPER(res#>>'{name,0,family}') as contained_practitioner_lname
      , UPPER(res#>>'{name,0,given,0}') as contained_practitioner_fname
      , UPPER(res#>>'{name,0,given,1}') as contained_practitioner_mname
      , UPPER(res#>>'{name,0,suffix,0}') as contained_practitioner_suffix
      , UPPER(res#>>'{address, 0, line, 0}') as contained_practitioner_address_street	  		
      , UPPER(res#>>'{address, 0, line, 1}')  as contained_practitioner_address_street_2
      , UPPER(res#>>'{address, 0, line, 2}')  as contained_practitioner_address_street_3
      , UPPER(res#>>'{address, 0, city}') as contained_practitioner_address_city
      , UPPER(res#>>'{address, 0, state}')  as contained_practitioner_address_state
      , res#>>'{address, 0, postalCode}'  as contained_practitioner_address_postalcode
      , UPPER(res#>>'{address, 0, country}')  as contained_practitioner_address_country
      , UPPER(res#>>'{address, 0, use}')  as contained_practitioner_address_use
      , res#>>'{identifier, 0, value}' as contained_practitioner_identifier_value            
      , res#>>'{identifier, 0, system}' as contained_practitioner_identifier_system
      , txid
      from pract
)
select distinct *
from flat_pract

v3.sql

WITH
maxtx AS (
    select max(txid) as mtx
      from (
        select max(txid) txid from assembly.encounter_contained_practitioner
         union
        select 0 as txid
      ) _
),
src AS (
  SELECT * FROM public.encounter src
  where resource->'contained' is not null
    and knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]') is not null 
    and txid > (select mtx from maxtx limit 1)
  ORDER BY txid asc
  limit 1000
), pract AS (
  SELECT
    id, txid, resource_type,
    unnest(knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]')) as res
    FROM  src
)
select count(*)
from pract

v4.sql

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

v5.sql

WITH src AS (
  SELECT * FROM public.encounter src
  where resource->'contained' is not null
    and knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]') is not null 
    and txid > 0
  ORDER BY txid asc
  limit 1000
), pract AS (
  SELECT
    id, txid, resource_type, res
    FROM  src, jsonb_array_elements(resource->'contained')  res
    where res->>'resourceType' = 'Practitioner'
)
select count(*)
from pract

v6.sql

SELECT * FROM public.encounter src
  where resource->'contained' is not null
    and knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]') is not null 
    and txid > 0
  ORDER BY txid asc
  limit 1000

v7.sql

-- wrong query
SELECT max(jsonb_array_length(resource->'contained'))
FROM public.encounter src
  where resource->'contained' is not null
    and knife_extract(resource, '[["contained", {"resourceType": "Practitioner"}]]') is not null 
    and txid > 0
  ORDER BY txid asc
  limit 1000

v8.sql

SELECT * FROM public.encounter src
  where resource->'contained' is not null
    and (resource->'contained') @> '[{"resourceType": "Practitioner"}]'
    and txid > 0
  ORDER BY txid asc
  limit 1000

v9.sql

select count(*) from (
  select jsonb_array_elements(resource->'contained')
  from encounter
) _