delete_dups.sql

DELETE FROM import.filepaths_1
WHERE data->>'repo' IN
(SELECT data->>'repo'
 FROM (SELECT data->>'repo', ROW_NUMBER() OVER (partition BY data->>'repo' ORDER BY data->>'repo') AS rnum
       FROM import.filepaths_1) t
 WHERE t.rnum > 1
);

insert.sql

insert into repos
select data->>'repo' as repo,
sanitize_formatted_number(data->'metadata'->'summary'->>'branches') as branches,
sanitize_formatted_number(data->'metadata'->'summary'->>'releases') as releases,
sanitize_formatted_number(data->'metadata'->'summary'->>'contributors') as contributors,
sanitize_formatted_number(data->'metadata'->'summary'->>'commits') as commits,
sanitize_formatted_number(data->'metadata'->'social_counts'->>'watchers') as watchers,
sanitize_formatted_number(data->'metadata'->'social_counts'->>'forks') as forks,
sanitize_formatted_number(data->'metadata'->'social_counts'->>'stars') as stars,
(data->'metadata'->'language_statistics'->0->>0) as lang1_name,
(data->'metadata'->'language_statistics'->0->>1)::decimal as lang1_percent,
(data->'metadata'->'language_statistics'->1->>0) as lang21_name,
(data->'metadata'->'language_statistics'->1->>1)::decimal as lang2_percent,
(data->'metadata'->'language_statistics'->2->>0) as lang3_name,
(data->'metadata'->'language_statistics'->2->>1)::decimal as lang3_percent,
(data->'metadata'->'language_statistics'->3->>0) as lang4_name,
(data->'metadata'->'language_statistics'->3->>1)::decimal as lang4_percent,
(data->'metadata'->'language_statistics'->4->>0) as lang5_name,
(data->'metadata'->'language_statistics'->4->>1)::decimal as lang5_percent,
(data->'metadata'->'language_statistics'->5->>0) as lang6_name,
(data->'metadata'->'language_statistics'->5->>1)::decimal as lang6_percent,
(data->'metadata'->'language_statistics'->6->>0) as lang7_name,
(data->'metadata'->'language_statistics'->6->>1)::decimal as lang7_percent
from import.filepaths_1
where not exists (select repo from repos where repo = data->>'repo')

schema.sql

create table repos (
repo varchar(255) primary key,
branches int,
releases int,
contributors int,
commits int,
watchers int,
forks int,
stars int,
lang1_name varchar(50),
lang1_percent decimal,
lang2_name varchar(50),
lang2_percent decimal,
lang3_name varchar(50),
lang3_percent decimal,
lang4_name varchar(50),
lang4_percent decimal,
lang5_name varchar(50),
lang5_percent decimal,
lang6_name varchar(50),
lang6_percent decimal,
lang7_name varchar(50),
lang7_percent decimal
)