CODEWITHSUNDEEP
sqlgoogle-bigquery
Antonio Rodriguez
Antonio Rodriguez

Reputation: 17

Query on Record - JSON data uploaded to BigQuery

enter image description here

I have this Big Query Record row that I am trying to query and get elements such as the doc_id and doc_type for each row. Since the value is in separate column how would I formulate the query to return the name and the value?.

ex result: doc_id doc_type 1 399239 document 2 999999 document If I do a query like:

select a.value as document_id, from 
`table_name`, UNNEST(jsonPayload.parameters) AS a where a.name = "doc_id"

I am able to get the doc_id for all values but can't add other things like doc_type.

Upvotes: 1

Views: 68

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

Below for BigQuery Standard SQL 

#standardSQL
SELECT  
  -- <any other needed fields here>, 
  (SELECT value FROM UNNEST(jsonPayload.parameters) WHERE name = 'doc_id') doc_id,
  (SELECT value FROM UNNEST(jsonPayload.parameters) WHERE name = 'doc_type') doc_type
FROM `project.dataset.table_name`

with result 

Row doc_id  doc_type     
1   399239  document     
2   999999  document

Or less verbose version with help of SQL UDF 

#standardSQL
CREATE TEMP FUNCTION Extract_Parameter(parameters ANY TYPE, key STRING) AS ((
SELECT value FROM UNNEST(parameters) WHERE name = key
));
SELECT  
  -- <any other needed fields here>, 
  Extract_Parameter(jsonPayload.parameters, 'doc_id') doc_id,
  Extract_Parameter(jsonPayload.parameters, 'doc_type') doc_type
FROM `project.dataset.table_name`

Upvotes: 2

Related Questions