BigQuery SQL to access first element of an array

[benjikj-cera]

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]

Could you provide a sample JSON (at least more than 1 element) and the expected output?

[benjikj-cera]

Sure, I have edited the original post above to include a better example and details of what I'm expecting out. Thanks.

[mars124 · Accepted solution]

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;

[benjikj-cera]

So interestingly, the query you provided does indeed work, however, it doesn't work when we apply it to our actual data (just comes out with NULLs). After investigation it appears the data type is actually a record/array, and therefore similar (but slightly easier) syntax can be applied of just: 

labels[0].value

So thanks for your suggestion it definitely lead me to the solution by being confident that was the right kind of syntax.

[caryna]

Hi @benjikj-cera,

You can also try using JSON_QUERY and JSON_VALUE. Here is a sample query:

 WITH -- set your table
  table_name AS (
   SELECT
  JSON_QUERY(
    '[{"key": "looker-context-user_id", "value": "1293"}, {"key": "looker-context-history_slug","value": "99eb46d83dd83d1f7525226c4fa4ef1b"}]',
 '$') AS label )
SELECT --extract value
  JSON_VALUE(label, '$[0].value') AS looker_context_user_id
FROM
  table_name;

I hope the above information is helpful.