Solved: BigQuery SQL to access first element of an array - Page 2

benjikj-cera · 10-07-2024 07:56 AM

Hi there,

I am wanting to try and avoid using an UNNEST function to access some array data due to the fact that it would explode out into duplicates, when all I actually care about is the first element of the array.

So I am trying to find out if there is any SQL functions that allow me to do this. I have played around with the JSON_QUERY function, but can't seem to get the right combination of syntax to extract what I would like; right now it only ever pushes out a NULL to the output.

Below is an example of the field in the table (called "labels"):

[{'key': 'looker-context-user_id', 'value': '1293'},
 {'key': 'looker-context-history_slug',
  'value': '99eb46d83dd83d1f7525226c4fa4ef1b'},
 {'key': 'looker-context-instance_slug',
  'value': '0e1a53a0e45ca686427e126045800c00'}]

My expected output would be another field (that I would label looker_context_user_id), with the value of 1293.

Note, that I know I can use a complicated combination of UNNEST and filtering out OFFSETs or ROW_NUMBERs or something like that, but I'm hoping there is a much more simple way with just simple BigQuery functions in on line to create one new field.

Any ideas?

mars124

Try this query:

WITH test AS (

SELECT JSON_ARRAY(

JSON_OBJECT('key', 'looker-context-user_id', 'value', '1293'),

JSON_OBJECT('key', 'looker-context-history_slug', 'value', '99eb46d83dd83d1f7525226c4fa4ef1b'),

JSON_OBJECT('key', 'looker-context-instance_slug', 'value', '0e1a53a0e45ca686427e126045800c00')

) AS json_sample

)

SELECT *, JSON_VALUE(json_sample, '$[0].value') AS first_value

FROM test;

View solution in original post