Bigquery - JSON_Extract All Elements from an Array

Extract multiple values from an array in JSON in BigQuery

Consider below approach

select 
  array(select json_extract_scalar(x, '$.Name') from unnest(json_extract_array(json_col, '$.info.music') || json_extract_array(json_col, '$.info.movie')) x) Name,
  array(select json_extract_scalar(x, '$.Singer') from unnest(json_extract_array(json_col, '$.info.music')) x) Singer
from data      

if applied to sample data in your question - output is

I just realized - you wanted comma separated list - so consider below then

select 
  (select string_agg(json_extract_scalar(x, '$.Name')) from unnest(json_extract_array(json_col, '$.info.music') || json_extract_array(json_col, '$.info.movie')) x) Name,
  (select string_agg(json_extract_scalar(x, '$.Singer')) from unnest(json_extract_array(json_col, '$.info.music')) x) Singer
from data     

with output

BigQuery - Extract data from an array of Json files

Below is for BigQuery Standard SQL

#standardSQL
CREATE TEMP FUNCTION json2array(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(json).map(x=>JSON.stringify(x));
"""; 
WITH `project.dataset.table` AS (
  SELECT "[{'a':1,'b':4, 'c':5}, {'a':0,'b':7, 'c':8},{'a':4,'b':9, 'c':12}]" json UNION ALL
  SELECT "[{'a':9,'b':10, 'c':9}]" UNION ALL
  SELECT "[{ 'a':5,'b':10, 'c':9}, {'a':1,'b':10, 'c':9}, {'a':7,'b':10, 'c':9}]"
)
SELECT json, JSON_EXTRACT_SCALAR(x, '$.b') AS b
FROM `project.dataset.table`,
  UNNEST(json2array(JSON_EXTRACT(json, '$'))) x
WHERE JSON_EXTRACT_SCALAR(x, '$.a') = '1'

with result

Row json                                                                        b    
1   [{'a':1,'b':4, 'c':5}, {'a':0,'b':7, 'c':8},{'a':4,'b':9, 'c':12}]          4    
2   [{ 'a':5,'b':10, 'c':9}, {'a':1,'b':10, 'c':9}, {'a':7,'b':10, 'c':9}]      10   

In case if you need to preserve all original rows - use below

#standardSQL
CREATE TEMP FUNCTION json2array(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(json).map(x=>JSON.stringify(x));
"""; 
WITH `project.dataset.table` AS (
  SELECT "[{'a':1,'b':4, 'c':5}, {'a':0,'b':7, 'c':8},{'a':4,'b':9, 'c':12}]" json UNION ALL
  SELECT "[{'a':9,'b':10, 'c':9}]" UNION ALL
  SELECT "[{ 'a':5,'b':10, 'c':9}, {'a':1,'b':10, 'c':9}, {'a':7,'b':10, 'c':9}]"
)
SELECT json, 
  (SELECT JSON_EXTRACT_SCALAR(x, '$.b')
  FROM UNNEST(json2array(JSON_EXTRACT(json, '$'))) x
  WHERE JSON_EXTRACT_SCALAR(x, '$.a') = '1'
  ) AS b
FROM `project.dataset.table`   

with result

Row json                                                                        b    
1   [{'a':1,'b':4, 'c':5}, {'a':0,'b':7, 'c':8},{'a':4,'b':9, 'c':12}]          4    
2   [{'a':9,'b':10, 'c':9}]                                                     null     
3   [{ 'a':5,'b':10, 'c':9}, {'a':1,'b':10, 'c':9}, {'a':7,'b':10, 'c':9}]      10  

How to extract JSON array stored as string in BigQuery

Have you tried json_extract_array

select json_extract_array(
  """[{"key":"Email","slug":"customer-email","value":"abc@gmail.com"},{"key":"Phone Number","slug":"mobile-phone-number","value":"123456789"},{"key":"First Name","slug":"first-  name","value":"abc"},{"key":"Last Name","slug":"last-name","value":"xyz"},{"key":"Date of birth","slug":"date-of-birth","value":"01/01/1990"}]""");

Bigquery - json_array extract and count elements from a nested arrays

Consider below approach

select key, array_length(split(values)) items
from `project.dataset.table`,
unnest(`bqutil.fn.json_extract_keys`(json_extract(col, '$.menu'))) key with offset 
join unnest(`bqutil.fn.json_extract_values`(json_extract(col, '$.menu'))) values with offset 
using(offset)          

if applied to sample data in your question - output is

BigQuery - JSON_EXTRACT only extracts first entry

Below is for BigQuery Standard SQL

#standardSQL
SELECT (
  SELECT STRING_AGG(JSON_EXTRACT_SCALAR(answer, '$.answer'), ' ,') 
  FROM UNNEST(JSON_EXTRACT_ARRAY(json_string)) answer
  ) AS answers 
FROM `project.dataset.table`  

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '[{"answer":"europe-austria-swiss","text":"Österreich, Schweiz"},{"answer":"europe-italy","text":"Italien"},{"answer":"europe-france","text":"Frankreich"}]' json_string
)
SELECT (
  SELECT STRING_AGG(JSON_EXTRACT_SCALAR(answer, '$.answer'), ' ,') 
  FROM UNNEST(JSON_EXTRACT_ARRAY(json_string)) answer
  ) AS answers 
FROM `project.dataset.table`   

with result

Row answers
1   europe-austria-swiss ,europe-italy ,europe-france

Bigquery - json_array extra multiple elements from a fields

Consider below

#standardSQL
with `project.dataset.table` as (
  select 1 id, '''
  {
    "entry": 1234,
    "comment_6789": {
        "_seconds": 1614864327,
        "_nanoseconds": 606000000,
        "message": "hello world"
    },
    "comment_4564564": {
        "_seconds": 1614864327,
        "_nanoseconds": 606000000,
        "message": "hello mars"
    }
  }  
  '''  col
)
select id, comment,
  json_value(kv, '$._seconds') seconds,
  json_value(kv, '$._nanoseconds') nanoseconds,
  json_value(kv, '$.message') message
from `project.dataset.table`,
unnest(regexp_extract_all(col, r'comment_\d+')) comment,
unnest([struct(regexp_extract(col, r'"' || comment || '": ({[^{}]+})') as kv)])      

with output

---

Related Topics