Extract value from JSON ARRAY in BigQuery

Question

I am trying to extract value from JSON ARRAY as below

  with `project.dataset.table` as (
  select '{"fruit":[{"apples":"5","oranges":"10","pear":"20"},
                    {"apples":"5","oranges":"4"},
                    {"apples":"5","oranges":"9","pear":"40"}]}' as json union all
  select '{"fruit":[{"lettuce":"7","kale": "8"}]}' 
)

select json, if(regexp_contains(json, '"apples":"5"'), (SELECT 
ARRAY_AGG(json_extract_scalar(arr, '$.oranges') ignore nulls)
from 
UNNEST(json_extract_ARRAY(json, '$.fruit')) as arr ), null) as oranges,
if(regexp_contains(json, '"apples":"5"'), (SELECT 
ARRAY_AGG(json_extract_scalar(arr, '$.pear') ignore nulls)
from 
UNNEST(json_extract_ARRAY(json, '$.fruit')) as arr ), null) as pear,
from `project.dataset.table`

It gives output as below

I am expecting output like

json	oranges	pear
{"fruit":[{"apples":"5","oranges":"10","pear":"20"},{"apples":"5","oranges":"4"},{"apples":"5","oranges":"9","pear":"40"}]}	10	20
	4	null
	9	40
{"fruit":[{"lettuce":"7","kale": "8"}]}.	null	null

Mikhail Berlyant · Accepted Answer · 2021-05-17 19:02:32Z

1

Consider below approach with more explicit alignment of respective entries

select json, 
  array(
    select 
      if(
        json_extract_scalar(val, '$.apples') = '5',
        struct(
          json_extract_scalar(val, '$.oranges') as oranges,
          json_extract_scalar(val, '$.pear') as pear
        ),
        struct(null, null)
      )
    from t.arr val
  ) fruits
from `project.dataset.table`, 
unnest([struct(json_extract_array(json, '$.fruit') as arr)]) t

It is less verbose and has output of fruits as a repeated record as opposed to two arrays
If applied to sample data in your question output is

In case if you really need to keep output is separate columns - use below

select json, 
  array(
    select 
      if(
        json_extract_scalar(val, '$.apples') = '5',
        ifnull(json_extract_scalar(val, '$.oranges'), '0'),
        '0'
      )
    from t.arr val
  ) as oranges,
  array(
    select 
      if(
        json_extract_scalar(val, '$.apples') = '5',
        ifnull(json_extract_scalar(val, '$.pear'), '0'),
        '0'
      )
    from t.arr val
  ) as pear
from `project.dataset.table`, 
unnest([struct(json_extract_array(json, '$.fruit') as arr)]) t

with output

answered May 17, 2021 at 19:02

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Panda Over a year ago

Thank you for you help :) It worked for me!

Sergey Geron · Accepted Answer · 2021-05-17 18:11:51Z

When working with array_agg

an error is raised if an array in the final query result contains a NULL element.

You can try using a string 'null' like this:

with `project.dataset.table` as (
  select '{"fruit":[{"apples":"5","oranges":"10","pear":"20"},{"apples":"5","oranges":"4"},{"apples":"5","oranges":"9","pear":"40"}]}' as json union all
  select '{"fruit":[{"lettuce":"7","kale": "8"}]}' 
)
select 
  json,
  if( regexp_contains(json, '"apples":"5"'),
    (SELECT ARRAY_AGG(ifnull(json_extract_scalar(arr, '$.oranges'), 'null')) from UNNEST(json_extract_ARRAY(json, '$.fruit')) as arr ),
    ['null']
  ) as oranges,
  if( regexp_contains(json, '"apples":"5"'),
    (SELECT ARRAY_AGG(ifnull(json_extract_scalar(arr, '$.pear'), 'null')) from UNNEST(json_extract_ARRAY(json, '$.fruit')) as arr ),
    ['null']
  ) as pear,
from `project.dataset.table`

Collectives™ on Stack Overflow

Extract value from JSON ARRAY in BigQuery

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related