Bigquery - json_array extra multiple elements from a fields

Question

I have a JSON field my table which looks like this. There could be any number of comments in each entry.

{
    "entry": 1234,
    "comment_6789": {
        "_seconds": 1614864327,
        "_nanoseconds": 606000000,
        "message": "hello world"
    },
    "comment_4564564": {
        "_seconds": 1614864327,
        "_nanoseconds": 606000000,
        "message": "hello mars"
    }
}

After reading this question I know I can use JSON_EXTRACT_ARRAY, but I'm a bit unsure on how to target each of the comment_ entries, since they will have different ids?

Mikhail Berlyant · Accepted Answer · 2021-03-25 21:00:40Z

2

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

answered Mar 25, 2021 at 21:00

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

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

Collectives™ on Stack Overflow

Bigquery - json_array extra multiple elements from a fields

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