| Sql |
select
a.rowid, a.id, a.vat_number, a.nome, a.nome as campo, a.valore_n from
(select rowid, id,
vat_number, (json_object_keys(financial_data::json))::text AS nome,
(financial_data::json -> json_object_keys(financial_data::json))::text AS valore_n
from (SELECT ROW_NUMBER() OVER() as rowid, id, vat_number, financial_data from (select id,
vat_number, jsonb_array_elements(financial_data) as financial_data
FROM reports)f) reports) a where a.valore_n !~ ('{')
union
select a.rowid,a.id, a.vat_number, a.nome::text,
(json_object_keys(a.valore_n::json))::text AS campo,
(a.valore_n::json -> json_object_keys(a.valore_n::json))::text AS valore_n from
(select rowid, id,
vat_number,
a.nome, a.valore_n from
(select rowid,id,
vat_number, (json_object_keys(financial_data::json))::text AS nome,
(financial_data::json -> json_object_keys(financial_data::json))::text AS valore_n
from (SELECT ROW_NUMBER() OVER() as rowid, id, vat_number, financial_data from (select id,
vat_number, jsonb_array_elements(financial_data) as financial_data
FROM reports)f) reports) a where a.valore_n ~ ('{')) a
|
