Array issue for BioClin_DBUrineID_v1r0

[KELSEYDOWLING7]

Hi Jake, for the variable d_928693120 is coming in I believe as an array. All the values looks like:
{integer:##########,string:null,provided:integer}
Instead of just the ten digit number. Do you mind adjusting the flattening for that variable?

[jacobmpeters]

Hi Kelsey. Thanks for letting me know this variable is causing problems for you. Unfortunately this isn't a flattening issue. It seems to be a data structure issue in Firestore. I'll let you know what I learn. I'll try to track down the problem and pass it off to Warren.

[KELSEYDOWLING7]

Hey Jake, IMS found that this issue is now showing up in the modules as well. For some reason I'm not able to tag you in the issue. episphere/questionnaire#402 Did you get a chance to look into this or is it still ongoing?

[jacobmpeters]

Hi Kelsey,

I reported these issues with the entity fields here initially: episphere/connect#938

And then we made a new issue specifically for the grid questions: episphere/connect#1049

There are a couple of simpler issues with biospecimen variables, but they have not been addressed yet as they were seen to be lower priority than the data structure issues with the grid questions.

I have a query available that screens for all abnormal data structure issues:

/*
Author: Jake Peters
Date: May 2024

GitHub Issue: https://github.com/episphere/connect/issues/938

Objective: Check BQ tables for columns with a data_type containing the substring "provided" 
          and columns with names containing the substrings 'provided|string|integer|float|object'. 
          This is used as a test for mixed-type data.

          The query is divided into two main sections:
          1. Mixed-Type Check for `Connect` Tables
          2. Mixed-Type Check for `FlatConnect` Tables
*/

## check_for_mixed_type_data

-- [1] Mixed-Type Check for `Connect` Tables --------------------------------------------------
-- Check the `Connect.INFORMATION_SCHEMA.COLUMNS` table in DEV, STG, and PROD environments 
-- for columns with a data_type containing the substring "provided".

SELECT 'DEV' AS environment, table_schema, table_name, column_name, data_type
FROM `nih-nci-dceg-connect-dev.Connect.INFORMATION_SCHEMA.COLUMNS`
WHERE REGEXP_CONTAINS(data_type, 'provided')

  UNION ALL

SELECT 'STG' AS environment, table_schema, table_name, column_name, data_type
FROM `nih-nci-dceg-connect-stg-5519.Connect.INFORMATION_SCHEMA.COLUMNS`
WHERE REGEXP_CONTAINS(data_type, 'provided')

  UNION ALL

SELECT 'PROD' AS environment, table_schema, table_name, column_name, data_type
FROM `nih-nci-dceg-connect-prod-6d04.Connect.INFORMATION_SCHEMA.COLUMNS`
WHERE REGEXP_CONTAINS(data_type, 'provided');

-- [2] Mixed-Type Check for `FlatConnect` Tables ---------------------------------------------
-- Check the `FlatConnect.INFORMATION_SCHEMA.COLUMNS` table in DEV, STG, and PROD environments 
-- for columns with names containing the substrings 'provided|string|integer|float|object'.

SELECT 'DEV' AS environment, table_schema, table_name, column_name
FROM `nih-nci-dceg-connect-dev.FlatConnect.INFORMATION_SCHEMA.COLUMNS`
WHERE REGEXP_CONTAINS(column_name, 'provided|string|integer|float|object')

  UNION ALL

SELECT 'STG' AS environment, table_schema, table_name, column_name
FROM `nih-nci-dceg-connect-stg-5519.FlatConnect.INFORMATION_SCHEMA.COLUMNS`
WHERE REGEXP_CONTAINS(column_name, 'provided|string|integer|float|object')

  UNION ALL

SELECT 'PROD' AS environment, table_schema, table_name, column_name
FROM `nih-nci-dceg-connect-prod-6d04.FlatConnect.INFORMATION_SCHEMA.COLUMNS`
WHERE REGEXP_CONTAINS(column_name, 'provided|string|integer|float|object');

[KELSEYDOWLING7]

Thanks! I'll let them know