Transform Array into columns in BigQuery

Question

I have a json string stored in a string column in BigQuery. There is an Array in it. I would like to pick some fields from array and write its value to BQ columns.

For example - Consider a below json stored in BQ

{
  "pool": "mypool",
  "statusCode": "0",
  "payloads": [
    {
      "name": "request",
      "fullpath": "com.gcp.commontools.edlpayload.EDLPayloadManagerTest$Request",
      "jsonPayload": {
        "body": "{\"data\":\"foo\"}"
      },
      "orientation": "REQUEST",
      "httpTransport": {
        "httpMethod": "POST",
        "headers": {
          "headers": {
            "a": "1"
          }
        },
        "sourceEndpoint": "/v1/foobar"
      }
    },
    {
      "name": "response",
      "fullpath": "com.gcp.commontools.edlpayload.EDLPayloadManagerTest$Response",
      "jsonPayload": {
        "body": "{\"data\":\"bar\"}"
      },
      "orientation": "RESPONSE",
      "httpTransport": {
        "headers": {
          "headers": {
            "b": "2"
          }
        },
        "httpResponseCode": 200
      }
    },
    {
      "name": "attributes",
      "fullpath": "java.util.HashMap",
      "nameValuePairs": {
        "data": {
          "one": "1"
        }
      },
      "orientation": "TRANSITORY"
    }
  ],
  "uuid": "11EC-C714-8ADE2390-9619-1B80E63968CC",
  "payloadName": "my-overall-name"
}

Consider a target BQ table schema is

pool, requestFullPath, requestPayload, responseFullPath, responsePayload

From the above json, i would like to pick few json elements and map there value to a column in BQ. Please note, array of payload will be dynamic in nature. There can be only 1 payload in the payloads array or there can be multiple. And the order of them is not fixed. For example, request payload can come at [0]th position, 1st position etc.

Answer 1

Consider below

select * from (
  select 
    json_value(json_col, '$.pool') as pool, 
    json_value(payload, '$.name') as name, 
    json_value(payload, '$.fullpath') as FullPath, 
    json_value(payload, '$.jsonPayload.body') as Payload, 
  from your_table t
  , unnest(json_extract_array(json_col, '$.payloads')) payload
)
pivot (any_value(FullPath) as FullPath, any_value(Payload) as Payload for name in ('request', 'response') )          

if applied to sample data in your question - output is