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

Bigquery one json data into bigquery table with elegant SQL

I have a bigquery table containing the below data(one column and one row)

Josn
{‘A’:{‘min’:0, ‘max’:10},’B’:{‘min’:1, ‘max’:15},’C’:{‘min’:10, ‘max’:18}}

I want to convert the json data into a table as shown below

Name min_value max_value
A 0 10
B 1 15
C 10 18

Currently, I only know the below method, does anyone have the elegant SQL to do the same thing?

SELECT 'A' AS name, 
    JSON_EXTRACT(Json, "$.A.min") as min_value, 
    JSON_EXTRACT(Json, "$.A.max") as max_value
FROM table_name
UNION ALL 
SELECT 'B' AS name, 
    JSON_EXTRACT(Json, "$.B.min") as min_value, 
    JSON_EXTRACT(Json, "$.B.max") as max_value
FROM table_name
UNION ALL 
SELECT 'C' AS name, 
    JSON_EXTRACT(Json, "$.C.min") as min_value, 
    JSON_EXTRACT(Json, "$.C.max") as max_value
FROM table_name
Tags:

2

Answers


  1. 0

    As commented by @hSin, you can find the query which follows the same logic of dynamic pattern matching in a json to make the resulting table in this stack link.

    As mentioned in the answer, you need to use REGEXP since it allows for a defined set of pattern matching both for including and excluding. Detailed answer can be found in the above mentioned stack link.

    Posting the answer as community wiki for the benefit of the community that might encounter this use case in the future. Feel free to edit this answer for additional information.

    Login or Signup to reply.
  2. 0

    Consider below approach

    select regexp_extract(row_data, r"^'(.+?)'") as name,
  regexp_extract(row_data, r"'min':(d+)") min_value,
  regexp_extract(row_data, r"'max':(d+)") max_value
from your_table,
unnest(regexp_extract_all(json_col, r"'.+?':{.+?}")) as row_data

    if applied to sample data in your question – output is

    enter image description here

    Another variation of above is

    select regexp_extract(row_data, r"^'(.+?)'") as name,
  json_value(min_max, '$.min') as min_value,
  json_value(min_max, '$.max') as max_value
from your_table,
unnest(regexp_extract_all(json_col, r"'.+?':{.+?}")) as row_data,
unnest([regexp_extract(row_data, r':({.+?})')]) min_max

    with same output

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

Back To Top
Search