How to query from a table in Bigquery with a dictionary with value of list of lists

Question

I have a bigquery table as following

idx	info
1	{'columns':['name','age'], 'data':[['Sheldon', 29], ['Raj',28]]}
2	{'columns':['name','age'], 'data':[['Leonard', 28], ['Howard',29]]}

info is a string column

I want to unnest it like the bellow

idx	name	age
1	Sheldon	29
1	Raj	28
2	Leonard	28
2	Howard	29

Answer 1

Consider also below solution (BigQuery) with column names also derived from data

SELECT * EXCEPT(pos) FROM (
  SELECT 
    idx, pos, TRIM(column, '"\'') AS column, TRIM(value, '[]"\'') AS value
  FROM data,
  UNNEST(JSON_EXTRACT_ARRAY(info, '$.data')) AS entry WITH OFFSET AS pos,
  UNNEST(SPLIT(entry)) AS value WITH offset
  LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(info, '$.columns')) AS column WITH offset 
  USING (offset)
)
PIVOT (ANY_VALUE(value) FOR column IN ("name", "age"))

If applied to sample data in your question, output is

If you have different or more columns - you just adjust in one place FOR column IN ("name", "age")) For example

WITH data AS (
  SELECT 1 AS idx, '{"columns":["name","age", "gender"],"data":[["Sheldon",29,"male"],["Raj",28,"female"]]}' AS info UNION ALL
  SELECT 2 AS idx, '{"columns":["name","age", "gender"],"data":[["Leonarda",28,"female"],["Howard",29,"male"]]}' AS info
)
SELECT * EXCEPT(pos) FROM (
  SELECT 
    idx, pos, TRIM(column, '"\'') AS column, TRIM(value, '[]"\'') AS value
  FROM data,
  UNNEST(JSON_EXTRACT_ARRAY(info, '$.data')) AS entry WITH OFFSET AS pos,
  UNNEST(SPLIT(entry)) AS value WITH offset
  LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(info, '$.columns')) AS column WITH offset 
  USING (offset)
)
PIVOT (ANY_VALUE(value) FOR column IN ("name", "age", "gender"))

gives you below output