时间戳

首页 > 解决方案 > Postgres 在视图中将 JSONB 单元格展平为行

json - Postgres 在视图中将 JSONB 单元格展平为行

问题描述

我有一个简单的 postgres 表：

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

这是一个简化的数据结构data：

{
  "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
      }
    ]
}

我想用以下列将其展平在一个视图中：

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

我的数组中的对象有一个标识符，我想将其附加到数组对象名称中，然后遍历对象中的其他键并制作列.

这个问题让我很接近： Postgres: Flatten aggregated key/value pairs from a JSONB field?

我不完全确定这在 plpgsql 之类的东西中是可能的，所以如果我只需要用 ruby​​/python 之类的脚本语言生成视图文本，然后从中创建一个视图，我可以接受。

理想情况下，我将能够使用类似jsonb_array_elementsandjsonb_each的东西来避免中间表（我当前的所有尝试都需要中间视图），但我还没有找到那个神奇的组合。

标签： jsonpostgresqlviewjsonb

解决方案

这不是关于扁平化 JSON 数组的一般问题，因为数组中隐藏着特定的逻辑。您可以在此函数中实现逻辑：

create or replace function flat_array(data jsonb, title text, item text)
returns jsonb language sql immutable as $$
    select jsonb_object_agg(format('%s_%s_%s', title, elem->>item, key), value)
    from jsonb_array_elements(data->title) as arr(elem)
    cross join jsonb_each(elem)
    where key <> item
$$;

查询：

select 
    jsonb_build_object('id', data->'id', 'date', data->'date') ||
    flat_array(data, 'well_report_table', 'element') ||
    flat_array(data, 'cost_report_table', 'item')
from my_table

给出对象：

{
    "id": 3,
    "date": "2019-01-01",
    "cost_report_table_fuel_charge": 6.30,
    "cost_report_table_lease_charge": 200,
    "well_report_table_butane_price": 3.50,
    "well_report_table_butane_yield": 6,
    "well_report_table_methane_price": 2.10,
    "well_report_table_methane_yield": 6,
    "well_report_table_pentane_price": 2.10,
    "well_report_table_pentane_yield": 6
}

可以按照从 JSONB 字段中展平聚合键/值对中描述的方式将其转换为表格视图？

推荐阅读