Tenho uma tabela simples do postgres:

    Column    │         Type         │ Modifiers
──────────────┼──────────────────────┼──────────────────────
 id           │ integer              │ not null default
 data         │ jsonb                │

Aqui está uma estrutura de dados simplificada paradata:

{
  "id": 3,
  "date": "2019-01-01",
  "well_report_table":
    [
      {"element": "methane",
      "yield": 6,
      "price": 2.10
      },
      {"element": "pentane",
      "yield": 6,
      "price": 2.10
      },
      {"element": "butane",
      "yield": 6,
      "price": 3.50
      }
    ],
  "cost_report_table":
    [
      {"item": "fuel",
      "charge": 6.30
      },
      {"item": "lease",
      "charge": 200
      }
    ]
}

Gostaria de achatar isso em uma exibição com as seguintes colunas:

id | date | well_report_table_methane_yield | well_report_table_methane_price | well_report_table_pentane_yield | well_report_table_pentane_price | well_report_table_butane_yield | well_report_table_butane_price |cost_report_table_fuel_charge | cost_report_table_lease_charge

Os objetos em minhas matrizes têm um identificador que eu gostaria de acrescentar ao nome do objeto da matriz e, em seguida, percorrer as outras chaves do objeto e criar colunas com.

Esta questão me aproxima:Postgres: Achatar pares de chave / valor agregados de um campo JSONB?

Não tenho certeza absoluta de que isso seja possível em algo como o plpgsql, por isso, se eu precisar gerar o texto da visualização em uma linguagem de script como ruby / python e criar uma visualização disso, estou de acordo com isso.

Ideally eu vou poder usar algo comojsonb_array_elements ejsonb_each para evitar tabelas intermediárias (todas as minhas tentativas atuais exigiram visualizações intermediárias), mas ainda não encontrei essa combinação mágic