Extract data from JSON column

i want to extract a value from a json column.

The schema is (- first level, — second level):

Column Name | Type | Mode

event_params RECORD NULLABLE

-key STRING NULLABLE

-value RECORD NULLABLE

--string_value STRING NULLABLE

--int_value INTEGER NULLABLE

Currently i extract a value this way:

SELECT
  (
  SELECT
    event_params.value.string_value
  FROM
    UNNEST(event_params) event_params
  WHERE
    event_params.key = 'user_id') AS user_id
FROM `my_db`

Is there a better way to handle the task?

Answer

Assuming that:

• event_params is an array of struct type.
• user_id is a unique key in each event_params

Following code style would be possible:

-- 1. use JOIN instead of scalar subquery in SELECT list
WITH my_db AS (
  SELECT [
    STRUCT('user_id' AS key, STRUCT('111111' AS string_value, 111111 AS int_value) AS value),
    STRUCT('cust_id' AS key, STRUCT('222222' AS string_value, 222222 AS int_value) AS value)
  ] AS event_params
)
SELECT e.value.string_value AS user_id
  FROM `my_db`, UNNEST(event_params) e WHERE e.key = 'user_id';

You can define a UDF to extract a value from an array of struct with key-value pair. Google provides some useful UDFs in public.

-- 2. Use a UDF to extract a value with key 'user_id'
WITH my_db AS (
  SELECT [
    STRUCT('user_id' AS key, STRUCT('111111' AS string_value, 111111 AS int_value) AS value),
    STRUCT('cust_id' AS key, STRUCT('222222' AS string_value, 222222 AS int_value) AS value)
  ] AS event_params
)
SELECT bqutil.fn.get_value('user_id', event_params).string_value AS user_id
  FROM `my_db`;

Above queries will return same output: