BigQuery: Extract values of selected keys from an array of json objects

Question

I have a json in bigQuery

{
  "actors": {
    "stooges": [
      {
        "id": 1,
        "name": "Larry"
      },
      {
        "id": 2,
        "name": "Curly"
      },
      {
        "id": 3,
        "name": "Moe"
      }
    ]
  }
}

How do I extract each of the .names in bigQuery.

["Larry", "Curly", "Moe"]

Here are some handy bigQuery compatible statements(based on above json).

-- Declaring a bigQuery variable 
DECLARE json_data JSON DEFAULT (SELECT PARSE_JSON('{ "actors": {"stooges": [{"id": 1,"name": "Larry"},{"id": 2,"name": "Curly"},{"id": 3,"name": "Moe"}]}}'));

-- Select statement. But this is no good for my use case since I don't want to specify element index ([0]) as the array size is dynamic
SELECT JSON_EXTRACT(json_data, '$.actors.stooges[0].name');

Scott B · Accepted Answer · 2022-09-23 00:22:21Z

5

You may try and consider below approach using JSON_EXTRACT_ARRAY() then unnest it and then use JSON_EXTRACT_SCALAR() to extract the values. You may refer to this documentation for more details in BigQuery JSON functions.

select ARRAY(
  SELECT JSON_EXTRACT_SCALAR(json_array, '$.name') from UNNEST(JSON_EXTRACT_ARRAY(json_data,"$.actors.stooges"))json_array
)extracted_names

Output:

edited Sep 23, 2022 at 0:22

answered Sep 22, 2022 at 6:51

Scott B

3,0541 gold badge11 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

BigQuery: Extract values of selected keys from an array of json objects

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related