How to use JSON_EXTRACT without having a key name?

Question

how do I extract value frm key "Nome" from JSON using JSON_EXTRACT in google bigquery? I cannot use the key 135 in the query because it is dynamic (Like this JSON_EXTRACT(vista, '$.Agencia.135.Nome'))

How to use JSON_EXTRACT without having a key '135' name?

JSON Record Sample:

{
    "Campanha": "Campanha A",
    "Ad": "Ad A",
    "Agencia": {
        "135": {
            "Celular": ".",
            "Codigo": "135",
            "CodigoPai": "105",
            "DDD": "00",
            "Email": "email-A@email.com",
            "Nome": "Nome A",
            "Fone": "00 0000.0000",
            "Fone2": ".",
            "Foto": "foto-A.jpg" 
        }
    }
}

Answer 1

Not sure if your json is formatted correctly. Is the key '135' an array? If so, format it properly and you can access it as the example below:

SELECT JSON_EXTRACT(json_text, '$.Agencia.135[1]') AS  nome
FROM UNNEST([
    '{"Agencia":{"135":[{"Codigo":"135"},{"Nome":"Nome A"}]}}'
]) AS json_text;

That would give you:

[
  {
    "nome": "{\"Nome\":\"Nome A\"}"
  }
]

For more references about the JSON_EXTRACT: https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_extract

Answer 2

Use below approach

execute immediate (
    select string_agg("select " || key || ''' key
    , JSON_EXTRACT_SCALAR(vista, '$.Agencia.''' || key || '''.Nome') AS Nome 
  from `project.dataset.table`''', " union all ")
  from `project.dataset.table`, unnest(regexp_extract_all(regexp_replace(JSON_EXTRACT(vista, '$.Agencia'), r':{.*?}+', ''), r'"(.*?)"')) key
);

If applied to sample data in your question - output is

Also, depends on your use case - you might try below option too

execute immediate (
    select 'select * from (' || string_agg("select " || key || ''' key
    , JSON_EXTRACT_SCALAR(vista, '$.Agencia.''' || key || '''.Nome') AS Nome 
  from `project.dataset.table`''', " union all ") || ') where not Nome is null'
  from `project.dataset.table`, unnest(regexp_extract_all(regexp_replace(JSON_EXTRACT(vista, '$.Agencia'), r':{.*?}+', ''), r'"(.*?)"')) key
);