CASE operator in BQ doesn't allow to use different type of arguments

Question

I need to query json strings based on different filters from the same table

CASE
    WHEN provider = 0 THEN JSON_VALUE_ARRAY (raw_data, '$.countries_served')
    WHEN provider = 1 THEN JSON_VALUE (raw_data,
'$.MerchantCountryISOAlpha3')
    WHEN provider = 2 THEN JSON_VALUE (raw_data, '$.operator.country.name')
    WHEN provider = 3 THEN JSON_VALUE (raw_data,
'$.countryName')
    WHEN provider = 4 THEN JSON_VALUE (raw_data, '$.countries')

This CASE statement returns an error:

No matching signature for operator CASE; all THEN/ELSE arguments must be coercible to a common type but found: ARRAY, STRING; actual argument types (WHEN THEN) ELSE: (BOOL ARRAY) (BOOL STRING) (BOOL STRING) (BOOL STRING) (BOOL STRING) NULL at [6:3]

Why so and how to make it work?

Answer 1

The issue here is that the first statement returns an ARRAY of countries, while all the other cases return a STRING with one country name.

The resulting column can only have one type, hence the error. To solve this you need to choose one type only, depending on what you intend to do with the data afterwards.

Maybe the most flexible option here would be to simply change JSON_VALUE_ARRAY function to JSON_QUERY: it returns a JSON-formatted string that still contains the array info:

WITH
  sample AS (
  SELECT 0 AS provider, '{"countries_served":["Japan", "France", "UK"]}' AS raw_data
  UNION ALL
  SELECT 4 AS provider, '{"countries":"Japan, France, UK"}' AS raw_data )

SELECT
  CASE
    WHEN provider = 0 THEN JSON_QUERY (raw_data, '$.countries_served')
    WHEN provider = 1 THEN JSON_VALUE (raw_data,'$.MerchantCountryISOAlpha3')
    WHEN provider = 2 THEN JSON_VALUE (raw_data, '$.operator.country.name')
    WHEN provider = 3 THEN JSON_VALUE (raw_data, '$.countryName')
    WHEN provider = 4 THEN JSON_VALUE (raw_data, '$.countries')
  END
  AS result
FROM
  sample

returns: