0

I'm generating a .json file output from Bigquery and attempting to only include NON NULL values within an "attributes" array/struct. My below query generates the STRUCT field with all the values including NULLs.

WITH t0 AS (
    SELECT 'd1' AS product_code, 'AA|BB' AS f1, '11|22|33' AS f2, NULL AS f3
    UNION ALL
    SELECT 'd2' AS product_code, 'ZZ' AS f1, '55|66' AS f2, 1 AS f3
)
,t1 AS (
    SELECT
        product_code
        ,SPLIT(f1, '|') AS f1
        ,SPLIT(f2, '|') AS f2
        ,f3
    FROM t0
)
SELECT
    product_code
    ,STRUCT(f1, f2, f3) AS attributes --IGNORE NULLS ?
FROM t1

The query returns in json:

[
  {
    "product_code": "d1",
    "attributes": {
      "f1": [
        "AA",
        "BB"
      ],
      "f2": [
        "11",
        "22",
        "33"
      ],
      "f3": null
    }
  },
  {
    "product_code": "d2",
    "attributes": {
      "f1": [
        "ZZ"
      ],
      "f2": [
        "55",
        "66"
      ],
      "f3": "1"
    }
  }
]

How can I remove f3 from the d1 array (null) but keep it within d2?

mkrieger1
  • 19,194
  • 5
  • 54
  • 65
Dan Grossnickle
  • 45
  • 1
  • 7

2 Answers2

0

Tried to replicate your issue, but there is no direct approach to remove f3 from the d1 array in Bigquery. As an alternative, you can refer to this SO post, it removes null values from JSON object using node.js. You may apply this JSON parser on the query returns (JSON format) from Bigquery.

Anjela B
  • 1,150
  • 1
  • 2
  • 7
0

Use a JavaScript UDF to output JSON (without nulls) like so:

CREATE TEMP FUNCTION
  stripNulls(input JSON)
  RETURNS JSON
  LANGUAGE js AS r"""
  return Object.keys(input).reduce((output, key) => {
    const value = input[key];
    return value !== null ? {...output, [key]: value} : output;
  }, {});
""";

SELECT stripNulls(TO_JSON(STRUCT('a' AS foo, 'b' AS bar, NULL AS biz)));

--> {"bar":"b","foo":"a"}
eezing
  • 125
  • 4