CODEWITHSUNDEEP
jsongoogle-bigquery
Nanda
Nanda

Reputation: 189

Getting Error when tried to read JSON structure in BigQuery table

Below is the JSON that I am trying to read and the CODE for your perusal:

I am just having Issue in reading "fieldorders" section, when it doesnt have any values. I still need to show them blank values if it doesnt have any structure. I am able to read couple of other objects which have multiple sections without any Issue. when we have an object with out any values, I have issue and I need to just place null values incase if i dont find any values in that object.

Getting the below Error:

**Failed to coerce output value false to type ARRAY**

Sample JSON file I am using to read the data:

{
  "projectnumber": "X.6001877",
  "operationnumber": "O.6001877.01",
  "opactivitynumber": "B.6001877.01.01",
  "jobtypes": null,
  "jobtypesinfo": [
    {
      "jobtype": "CC-SERV",
      "jobgroup": "CPS-CC",
      "staticattributes": [
        {
          "name": "OAJTOPT",
          "description": "OA Job Type OPT",
          "type": "Double",
          "value": 0.0,
          "uom": null
        }
      ]
    }
  ],
  "actualactivitystartdate": "2018-01-17T05:00:00",
  "actualactivityenddate": "2018-01-29T05:00:00",
  "serverdatetime": null,
  "ServerDateTime": "2019-01-20T16:36:48.106",
  "projectSettings": null,
  "customerContacts": null,
  "actualequipments": null,
  "welldetails": [
    {
      "Number": "1-1IH",
      "Name": "XXXX 58-4X",
      "State": "PL",
      "Country": "Col",
      "Field": "LABCD",
      "Uwi": null,
      "Environment": "Land",
      "WellId": "0065",
      "Latitude": 3.8,
      "Longitude": -72.2,
      "Type": null,
      "WaterDepth": null,
      "WellPlaceholderId": null,
      "IsNonMasteredWell": false
    }
  ],
  "lastopeventid": null,
  "personnelassignmentinfo": null,
  "status": null,
  "accountingunit": null,
  "erpsystem": "ITT",
  "CreatedDate": "2020-01-20T16:36:48.106",
  "CreatedBy": "ABCD11",
  "LastModifiedDate": "2020-01-20T16:36:48.106",
  "LastModifiedBy": "ABCD11",
  "Id": "A.6001877.01.01",
  "country": {
    "Code": "CO",
    "Name": "CoOOOOOO"
  },
  "attributes": {
    "Attributes": [
      {
        "AttributeName": "OAOPDXAS",
        "AttributeDescription": "Activity OPD",
        "DataType": "Integer",
        "UOMType": "Dimensionless",
        "BaseUnit": "",
        "IsCalculated": true,
        "Values": null
      },
      {
        "AttributeName": "OpActOPTime",
        "AttributeDescription": "OA Operating Time - OPT (HRS)",
        "DataType": "Float",
        "UOMType": "Dimensionless",
        "BaseUnit": "",
        "IsCalculated": true,
        "Values": null
      }
    ],
    "DailyAttributes": null,
    "MultiAttributes": null,
    "Id": "A.6001877.01.01"
  },
  "operationalevent": [
    {
      "operatingevent": {
        "projectnumber": "C.6001877",
        "operationnumber": "O.6001877.01",
        "operationactivitynumber": "X.6001877.01.01",
        "operationaleventdetails": {
          "status": null,
          "description": "Non-Operational Event",
          "plannedeventid": null,
          "jobgroup": null,
          "jobtype": null,
          "startdatetime": "2020-01-18T05:00:00",
          "enddatetime": "2020-01-15T05:00:00",
          "comments": "Non-Operational Event",
          "eventtype": "Project",
          "isdeleted": false,
          "category": "NonOperational",
          "islocked": false,
          "lockedon": "0001-01-01T05:00:00",
          "lockedby": null,
          "audittrailinfo": {
            "CreatedDate": "2020-01-20T15:36:17.816",
            "CreatedBy": "ABCD11",
            "LastModifiedDate": "2020-01-20T15:36:17.816",
            "LastModifiedBy": "ABCD1111",
            "Id": null
          },
          "personnel": {
            "assignment": [

            ]
          },
          "serverdatetime": "2018-01-20T16:36:56.185",
          "equipmentdata": {
            "equipmentassignments": [

            ]
          },
          "eventtypeattributes": null,
          "id": "E97A5DBC",
          "oesummary": null,
          "journal": null,
          "well": null,
          "isactive": true,
          "externaltransactionhistoryinfo": [
            {
              "status": "Pending",
              "message": null,
              "type": "MPT",
              "riteservicereporturl": null,
              "CreatedDate": "0001-01-01T00:00:00",
              "CreatedBy": null,
              "LastModifiedDate": "0001-01-01T00:00:00",
              "LastModifiedBy": null,
              "Id": null
            }
          ],
          "pnmconsumptiondata": {
            "pnmconsumptions": [

            ]
          }
        },
        "CreatedDate": "2018-01-20T16:36:56.185",
        "CreatedBy": "ABCD11",
        "LastModifiedDate": "2020-01-20T16:36:56.185",
        "LastModifiedBy": "ABCD11",
        "Id": "A.6001877.01.01_OperationalEvent_E97A5DBC"
      },
      "attributes": null
    }
  ],
  "attendance": [

  ],
  **"fieldorders": [

  ]**
}

BigQuery SQL Code:

    CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
    RETURNS ARRAY<STRING>
    LANGUAGE js AS """
            return jsonPath(JSON.parse(json), json_path);
    """
    OPTIONS (
        library="gs://json_temp/jsonpath-0.8.0.js"
    );

    SELECT job_id,oe_descr,
    attr_name,
    well_name,
    job_type,
    --field_id

    from lz.json_actuals,
    UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.operationalevent[*].operatingevent.operationaleventdetails.description')) oe_descr  with offset oedescr,
    UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.attributes.Attributes[*].AttributeName')) attr_name with offset attrb,
    UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.welldetails[*].Name')) Well_name with offset wll,
    UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.jobtypesinfo[*].jobtype')) job_type with offset jt,
    --UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.fieldorders[*].id')) field_id WITH OFFSET fld

  ;

Upvotes: 2

Views: 886

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below for BigQuery Standard SQL and should resolve your issue with empty object 

#standardSQL
CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
RETURNS ARRAY<string>
LANGUAGE js AS """
  var result = jsonPath(JSON.parse(json), json_path);
  if(result){return result;} 
  else {return [];}
"""
OPTIONS (
    library="gs://json_temp/jsonpath-0.8.0.js"
);
SELECT --job_id,
  oe_descr,
  attr_name,
  well_name,
  job_type,
  field_id
from `lz.json_actuals`,
UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.operationalevent[*].operatingevent.operationaleventdetails.description')) oe_descr  with offset oedescr,
UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.attributes.Attributes[*].AttributeName')) attr_name with offset attrb,
UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.welldetails[*].Name')) Well_name with offset wll,
UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.jobtypesinfo[*].jobtype')) job_type with offset jt
LEFT JOIN UNNEST(CUSTOM_JSON_EXTRACT(conv_column, '$.fieldorders[*].id')) field_id WITH OFFSET fld

if to apply to sample data in your question - result is 

Row oe_descr                    attr_name   well_name   job_type    field_id     
1   Non-Operational Event       OAOPDXAS    XXXX 58-4X  CC-SERV     null     
2   Non-Operational Event       OpActOPTime XXXX 58-4X  CC-SERV     null

Upvotes: 1

Related Questions