CODEWITHSUNDEEP
jsongoogle-bigquery
user18162889
user18162889

Reputation: 123

How to extract all the keys in a JSON with BigQuery

I have a json data in Bigquery like this:

**document_id   time_in_pending_per_user**
  1             {"quqtC1DfyAk0d5bMi7GIE7":1735,"XmrBJS4hnqLLyDH1W5X7z2":6150,"system":0}

and I want to transform this data like this to parse:

**user ID               time_in_pending_per_user**   document_id
quqtC1DfyAk0d5bMi7GIE7  1735                         1
XmrBJS4hnqLLyDH1W5X7z2  6150                         1
system                  0                            1

Can you help me? thanks!

Upvotes: 1

Views: 177

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Consider below

create temp function  extract_keys(input string) returns array<string> language js as """
  return Object.keys(JSON.parse(input));
  """;
create temp function  extract_values(input string) returns array<string> language js as """
  return Object.values(JSON.parse(input));
  """;
select user_id, val as time_in_pending_per_user, document_id
from your_table,
unnest(extract_keys(time_in_pending_per_user)) user_id with offset
join unnest(extract_values(time_in_pending_per_user)) val with offset
using(offset)

if applied to sample data in your question - output is

enter image description here

Upvotes: 2

Related Questions