Question 185227 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – Extract wildcard key in Bigquery

I have a table with the following structure in bigquery,

id desc
btjap {"val":{"60fc-4955-8d9b":{"key":"top"},"4f96-4c9e-88f0":{"key":"left"}}}
fxlol {"val":{"c783-9342-h73s":{"key":"up"},"83hs-eudu-s839":{"key":"above"}, "37s9-dh3u-39sr":{"key":"right"}}}
nretx {"val":{}}

The desc column contains multiple wildcard keys as value for the val key .

I want to unpack the data into the following table

id code key
btjap 60fc-4955-8d9b top
btjap 4f96-4c9e-88f0 left
fxlol c783-9342-h73s up
fxlol 83hs-eudu-s839 above
fxlol 37s9-dh3u-39sr right
nretx

any suggestions

Tags:

2

Answers


  1. 0

    This can be done using json_extract_keys to extract keys (code) from the json, then to access to the nested elements we can use REGEXP_EXTRACT

    with mytable as (
  select 'btjap' as id,     '{"val":{"60fc-4955-8d9b":{"key":"top"},"4f96-4c9e-88f0":{"key":"left"}}}' as descr union all
  select 'fxlol',   '{"val":{"c783-9342-h73s":{"key":"up"},"83hs-eudu-s839":{"key":"above"}, "37s9-dh3u-39sr":{"key":"right"}}}' union all
  select 'nretx',   '{"val":{}}'
)
select id, code, REGEXP_EXTRACT(descr, FORMAT('"%s":{"key":"([a-z1-9]*)".*', code)) key
from mytable t,
UNNEST(bqutil.fn.json_extract_keys(JSON_QUERY(t.descr, '$.val'))) as code;

    In case the function json_extract_keys is not found in your location then you can create an equivalent UDF :

    CREATE TEMP FUNCTION json_extract_keys(json STRING) RETURNS ARRAY<STRING> LANGUAGE js AS """
  try {
    return Object.keys(JSON.parse(json));
  } catch {
    return null;
  }
""";

with mytable as (
  select 'btjap' as id,     '{"val":{"60fc-4955-8d9b":{"key":"top"},"4f96-4c9e-88f0":{"key":"left"}}}' as descr union all
  select 'fxlol',   '{"val":{"c783-9342-h73s":{"key":"up"},"83hs-eudu-s839":{"key":"above"}, "37s9-dh3u-39sr":{"key":"right"}}}' union all
  select 'nretx',   '{"val":{}}'
)
select id, code, REGEXP_EXTRACT(descr, FORMAT('"%s":{"key":"([a-z1-9]*)".*', code)) key
from mytable t,
UNNEST(json_extract_keys(JSON_QUERY(t.descr, '$.val'))) as code;

    Result :

    enter image description here

    Login or Signup to reply.
  2. 0

    Similar to @SelVazi’s answer, but you can use UDFs as well and need to LEFT JOIN flattened arrays for your expected result.

    CREATE TEMP FUNCTION keys(json STRING) RETURNS ARRAY<STRING> LANGUAGE js AS """
  return Object.keys(JSON.parse(json));
""";

CREATE TEMP FUNCTION values(json STRING) RETURNS ARRAY<STRING> LANGUAGE js AS """
  return Object.values(JSON.parse(json)).map(e => e.key);
""";


WITH sample_table AS (
  SELECT 'btjap' id, '{"val":{"60fc-4955-8d9b":{"key":"top"},"4f96-4c9e-88f0":{"key":"left"}}}' `desc` UNION ALL
  SELECT 'fxlol', '{"val":{"c783-9342-h73s":{"key":"up"},"83hs-eudu-s839":{"key":"above"}, "37s9-dh3u-39sr":{"key":"right"}}}' UNION ALL
  SELECT 'nretx', '{"val":{}}'
)
SELECT id, code, key
  FROM sample_table
  LEFT JOIN UNNEST(keys(JSON_QUERY(`desc`, '$.val'))) code WITH offset
  LEFT JOIN UNNEST(values(JSON_QUERY(`desc`, '$.val'))) key WITH offset USING (offset);

    query results

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search