PostgreSQL to BigQuery notes - kind-lab/mimic-fhir GitHub Wiki

• JSONB_BUILD_ARRAY
  • ARRAY(SELECT AS STRUCT ...)
  • ARRAY_AGG - requires more than one element of the array
  • []

TODO:

• Not sure how to create UUID-5 in BigQuery
  • BigQuery functions possible, but no variables inside them. So fairly tough/messy to recreate the UUID5 query without a couple variables
  • Standard sql functions allowed, so could just use the same one we generated for SEDAR uuids (standard sql may be slower)
  • BigQuery functions can be written in JavaScript, but again no base UUID5 in javascript so would need to rewrite anyways
• Drop null values from final JSON, FHIR does not carry these over. TO_JSON_STRING does not inherently do this