example for unpacking claims

[donna1up]

`WITH
eob as 
(select id 
,identifier
,patient
,type 
,supportinginfo
,diagnosis
,procedure
,careteam
,total
,item
,billablePeriod
,payment
,created
,meta
from main.fhir_r4_explanationofbenefit
     WHERE  ("date"(meta.lastUpdated) > CAST((current_date - INTERVAL  '120' DAY) AS timestamp))
     )
 , item_rev AS (
   SELECT
     a6.id
   , b6.n seq
   , t6.item_revenue_coding_x.code code
   FROM
     ((eob a6
   CROSS JOIN UNNEST(item) WITH ORDINALITY b6 (item, n))
   CROSS JOIN UNNEST(b6.item.revenue.coding) t6 (item_revenue_coding_x))
   GROUP BY 1, 2, 3
) 
, item_proc AS (
   SELECT
     a7.id
   , b7.n seq
   , t6.item_productOrService_coding_x.code code
   FROM
     ((eob a7
   CROSS JOIN UNNEST(item) WITH ORDINALITY b7 (item, n))
   CROSS JOIN UNNEST(b7.item.productOrService.coding) t6 (item_productOrService_coding_x))
   WHERE (t6.item_productOrService_coding_x.code IS NOT NULL)
   GROUP BY 1, 2, 3
)
SELECT
  z.id
, item_x."sequence" line_item
, (CASE WHEN (b1.code IN ('0450', '0451', '0452', '0456', '0459', '0981')) THEN 1 WHEN (c1.code LIKE '9928%') THEN 1 ELSE 0 END) ed_item_flag
, b1.code revenue_code
, c1.code procedure_code
FROM
  (((eob z
CROSS JOIN UNNEST(z.item) t4 (item_x))
LEFT JOIN item_rev b1 ON ((z.id = b1.id) AND (item_x."sequence" = b1.seq)))
LEFT JOIN item_proc c1 ON ((z.id = c1.id) AND (item_x."sequence" = c1.seq)))
group by 1,2,3,4,5
`

[niquola]

What is the database engine for this query?

[donna1up]

serverless AWS Athena, connected with DBeaver.

[dkapitan]

Perhaps it would help to compile list of query patterns like this one and add it to the technology matrix. I am happy to initiate that.

Hadn't used the cross join unnest pattern myself, but it is supported by duckdb, BigQuery and PostgreSQL. So this seems quite a common pattern.

[donna1up]

My other example is better representing CROSS JOIN UNNEST, this is an example from a larger complex query on alternatives to that like a lateral view explode approach, unpacking individually with ordinality- then bringing it all together in a left join.

Depending on which part of a claim you are querying, the lateral view explode approach worked well to unpack supportinginfo, diagnosis, and item unpacking objects from different arrays that are not 1:1 with the item array.
https://theleftjoin.com/how-to-explode-arrays-with-presto/

[donna1up]

Perhaps it would help to compile list of query patterns like this one and add it to the technology matrix. I am happy to initiate that.

Hadn't used the cross join unnest pattern myself, but it is supported by duckdb, BigQuery and PostgreSQL. So this seems quite a common pattern.

this is a better example of cross join unnest with quality use case #63