Unnest array / JSON in Bigquery - Get value from key

Dhiego TS 0
Viewed 26

I have an array like this

[{"name": "Nome da Empresa", "value": "Land ", "updated_at": "2022-09-02T22:30:58Z"}, {"name": "Nome do Representante", "value": "Thomas GT", "updated_at": "2022-09-02T22:30:58Z"}, {"name": "Email Representante", "value": "p@xyz.com", "updated_at": "2022-09-02T22:30:58Z"}, {"name": "Qual o plano do cliente?", "value": "Go", "updated_at": "2022-09-02T22:31:12Z"},{"name": "Forma de pagamento", "value": "Internet Banking", "updated_at": "2022-09-16T14:09:53Z"}, {"name": "Valor total da guia", "value": "227,63", "updated_at": "2022-09-16T14:09:59Z"}]

I'm trying to get values from some "fields" like Nome da Empresa or Email Representante.

I've already tried use json_extract_scalar or unnest. With json_extract_scalar returns column with no values (blank) and with unnest returns error Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRING

Query 1:

select 

id,
fields,
json_extract_scalar(fields,'$.Email Representante') as categorias,
json_value(fields,'$.Nome da Empresa') as teste

from mytable

Query 2:

SELECT

id,
fields

from pipefy.cards_startup_pack, UNNEST(fields)

Any ideas? Thanks a lot!

google-bigquery sql arrays
1 Answers

You may try below query.

Query 1:
SELECT (SELECT JSON_VALUE(f, '$.value') 
          FROM UNNEST(JSON_QUERY_ARRAY(t.fields)) f 
         WHERE JSON_VALUE(f, '$.name') = 'Nome da Empresa'
       ) AS teste,
       (SELECT JSON_VALUE(f, '$.value') 
          FROM UNNEST(JSON_QUERY_ARRAY(t.fields)) f 
         WHERE JSON_VALUE(f, '$.name') = 'Email Representante'
       ) AS categorias,       
  FROM mytable t;

# Query results
+-------+------------+
| teste | categorias |
+-------+------------+
| Land  | p@xyz.com  |
+-------+------------+
Query 2:
SELECT JSON_VALUE(f, '$.name') name, JSON_VALUE(f, '$.value') value
  FROM mytable, UNNEST(JSON_QUERY_ARRAY(fields)) f;

# Query results
+--------------------------+------------------+
|           name           |      value       |
+--------------------------+------------------+
| Nome da Empresa          | Land             |
| Nome do Representante    | Thomas GT        |
| Email Representante      | p@xyz.com        |
| Qual o plano do cliente? | Go               |
| Forma de pagamento       | Internet Banking |
| Valor total da guia      | "227,63"         |
+--------------------------+------------------+
Jaytiger0
Related