Bigquery - json_array extract and count elements from a nested arrays

Question

I have a JSON field my table which looks like this. There could be any number of menu categories and any number of items

{
"menu": {
    "salad": [
        {
            "item": "russian salad"
        },
        {
            "item": "french salad"
        }
    ],
    "soups": [
        {
            "item": "french soup"
        },
        {
            "item": "english soup"
        },
        {
            "item": "english soup"
        }
    ]
}

There could be any number of menu category (salad, soup) plus any number of items within each category

salad | 2
soups | 3

I've looked at my own previous question, but unsure how I can modify to accommodate for any number of categories and items?

Mikhail Berlyant · Accepted Answer · 2022-01-05 18:27:44Z

3

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

answered Jan 5, 2022 at 18:27

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

1 Comment

Tom Over a year ago

Thanks once again. Just to note for anyone else struggling with bgutil, it's not currently (Jan 2021) available outside the US as a public dataset, it needs to be deployed, github.com/GoogleCloudPlatform/bigquery-utils/issues/252

Collectives™ on Stack Overflow

Bigquery - json_array extract and count elements from a nested arrays

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related