Youtube Tutorial (Hindi, Auto Dubbed in English) : https://youtu.be/zE3EXQhs3js

Script link: https://colab.research.google.com/drive/19iVBkojfPyiNXyFgz3KI3_5zRm9gc2rv?authuser=1#scrollTo=xkhIWgrUp_SQ

SQL Code:

create or replace table lok_sabha.dim_src_member_attendence as
with base as 
  ( SELECT lok_sabha
    ,session
    ,dateOfAttendance
    ,attendence_data
  FROM lok_sabha.src_member_attendence,
  unnest(JSON_EXTRACT_ARRAY(member_level_attendence)) as attendence_data)

select *
  ,JSON_EXTRACT_SCALAR(attendence_data,'$.mpsno') mpsno
  ,JSON_EXTRACT_SCALAR(attendence_data,'$.division') division
  ,JSON_EXTRACT_SCALAR(attendence_data,'$.memberName') memberName
  ,JSON_EXTRACT_SCALAR(attendence_data,'$.attendanceStatus') attendanceStatus
from base

create or replace table lok_sabha.dim_src_loksabha_session_dates as 
SELECT * 
  ,JSON_EXTRACT_SCALAR(api_data,'$.presentCount') as presentCount
  ,JSON_EXTRACT_SCALAR(api_data,'$.absentCount') as absentCount
FROM `lok_sabha.src_loksabha_session_dates`;