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

Google Cloud Data Analytics
1

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

2

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.