Datawise
BigQuery JSON

LAX JSON conversion functions in BigQuery

Constantin LunguUpdated 1 min read

Photo by David Hunter on Unsplash

So if you're looking to decompress after a long week and relax, check out the LAX conversion functions for handling JSON conversions in BigQuery.

There are 4 separate functions: LAX_STRING, LAX_BOOL, LAX_FLOAT64, LAX_INT64 - with each one of them attempting to convert a JSON value into the respective datatype.

This is a bit like using SAFE_CAST - you won't get an error if the casting fails, just a NULL (so watch out, check the comments for an example when this can come to back to bite you).

Just note that even JSON-like string won't work as an input, it only works for the native JSON type.

As usual, watch out because these conversion functions might work differently as how you'd expect. SAFE_CAST('1' AS BOOL) => NULL but SAFE_CAST(1 AS BOOL) => TRUE.

DECLARE json_data ARRAY<JSON> DEFAULT
[
 JSON '{"name": "apple", "pack_size": 4, "price": "7.1","type": "pome fruit", "is_local": "TRUE", "is_sweet": 1 }',
 JSON '{"name": "lemon", "pack_size": 5, "price": "12.5", "type": "citrus", "is_local": "false", "is_sweet": 0 }',
 JSON '{"name": "orange","pack_size": 3, "price": "9.99", "type": "citrus", "is_local": "", "is_sweet": 1}'
];

SELECT
  LAX_STRING(fruit.name) AS fruit_name,
  LAX_BOOL(fruit.is_sweet) AS is_sweet,
  LAX_BOOL(fruit.is_local) AS is_local,
  LAX_FLOAT64(fruit.price) AS price,
  LAX_INT64(fruit.pack_size) AS pack_size

FROM UNNEST(json_data) AS fruit

BigQuery results: apple has is_sweet true, is_local true, price 7.1, pack_size 4; lemon false, false, 12.5, 5; orange true, null, 9.99, 3.

Enjoyed this? Here are some related articles you might find useful: