with
lines
as
(
select
json_array_elements(forbes2k_json::json)
as
company
from
forbes2k_json
),
entries
as
(
select
json_array_elements(company)::text
as
entry,
row_number() over ()
as
entrynr
from
lines
),
fields
as
(
select
*,
row_number() over (partition
by
entrynr)
as
fieldnr
from
entries
)
select
f1.entry::
integer
as
rank,
regexp_replace(f2.entry::text,
'^"(.*)"$'
,
'\1'
)
as
urlname,
regexp_replace(f3.entry::text,
'^"(.*)"$'
,
'\1'
)
as
company,
regexp_replace(f4.entry::text,
'^"(.*)"$'
,
'\1'
)
as
logoname,
f5.entry::
integer
as
sort,
regexp_replace(regexp_replace(f6.entry::text,
','
,
''
),
'^"(.*)"$'
,
'\1'
)::
numeric
(10,3)
as
sales,
regexp_replace(regexp_replace(f7.entry::text,
','
,
''
),
'^"(.*)"$'
,
'\1'
)::
numeric
(10,3)
as
profits,
regexp_replace(regexp_replace(f8.entry::text,
','
,
''
),
'^"(.*)"$'
,
'\1'
)::
numeric
(10,3)
as
assets,
regexp_replace(regexp_replace(f9.entry::text,
','
,
''
),
'^"(.*)"$'
,
'\1'
)::
numeric
(10,3)
as
marketvalue,
regexp_replace(f10.entry::text,
'^"(.*)"$'
,
'\1'
)
as
country,
regexp_replace(f11.entry::text,
'^"(.*)"$'
,
'\1'
)
as
industry,
regexp_replace(f12.entry::text,
'^"(.*)"$'
,
'\1'
)
as
us_state
from
fields f1
inner
join
fields f2
on
f2.entrynr = f1.entrynr
and
f2.fieldnr = 2
inner
join
fields f3
on
f3.entrynr = f1.entrynr
and
f3.fieldnr = 3
inner
join
fields f4
on
f4.entrynr = f1.entrynr
and
f4.fieldnr = 4
inner
join
fields f5
on
f5.entrynr = f1.entrynr
and
f5.fieldnr = 5
inner
join
fields f6
on
f6.entrynr = f1.entrynr
and
f6.fieldnr = 6
inner
join
fields f7
on
f7.entrynr = f1.entrynr
and
f7.fieldnr = 7
inner
join
fields f8
on
f8.entrynr = f1.entrynr
and
f8.fieldnr = 8
inner
join
fields f9
on
f9.entrynr = f1.entrynr
and
f9.fieldnr = 9
inner
join
fields f10
on
f10.entrynr = f1.entrynr
and
f10.fieldnr = 10
inner
join
fields f11
on
f11.entrynr = f1.entrynr
and
f11.fieldnr = 11
inner
join
fields f12
on
f12.entrynr = f1.entrynr
and
f12.fieldnr = 12
where
f1.fieldnr = 1
order
by
rank, sort