How to extract the productType variable from a json file in big query

[XZ]

JSON format as follows:

"event_data_inputs": [
  {  "name": "assessmentrequest.session.1.identifierkey",
     "value": "ProductType" },
  { "name": "rulesresponse.assessmentrequest.session.1.identifiervalue",
    "value": "PAID" }, 
....
  ]

I tried the following query but failed: 

select distinct time as dt, referenceid, 

(select ds.value from unnest(event_data_inputs) ds where ds.name =( select concat('rulesresponse.',left(r.name, length(r.name)-3),'value') from unnest(event_data_inputs)  r where UPPER(r.value) =UPPER("ProductType") )) as ProductType

from aaa

[ms4446]

To accurately extract the "productType" variable from your JSON structure in BigQuery, you should use a query that handles the nested array event_data_inputs. The following SQL query is designed for this purpose:

SELECT
 DISTINCT time AS dt,
 referenceid,
 (
  SELECT value
  FROM UNNEST(event_data_inputs)
  WHERE name = 'assessmentrequest.session.1.identifierkey'
   AND UPPER(value) = UPPER('ProductType')
 ) AS ProductType
FROM 
 aaa;

Here's a breakdown of how this query works:

• FROM aaa: This specifies that the data is being queried from the aaa table.
• UNNEST(event_data_inputs): This function flattens the event_data_inputs array into individual rows, making it easier to query each JSON object within the array.
• WHERE name = 'assessmentrequest.session.1.identifierkey' AND UPPER(value) = UPPER('ProductType'): This condition filters the rows to find the object where the name field matches 'assessmentrequest.session.1.identifierkey' and its corresponding value is 'ProductType'.
• SELECT DISTINCT time AS dt, referenceid, ...: This part of the query selects the distinct time values (aliased as dt), referenceid, and the extracted ProductType.

The result of this query will be a table with columns dt (unique time values), referenceid, and ProductType (the extracted value corresponding to 'ProductType'). This approach ensures accurate extraction of the desired data from a nested JSON array structure in BigQuery.