json - 将 PostgreSQL 中的 JSON 记录转换为表

Question

我在 PostgreSQL 中有一个 table_1，其中 column_1 和列中的几条记录包含具有以下结构的嵌套 json:

{"parent_1": {
    "child_1": [10.477058081076123, 12.570963520289965, 11.74866851427825],
    "child_2": [14.174190848828983, 19.3920283059595, 17.6712937162821]
},
"parent_2": {
    "child_1_1": [24.100638151071383, 28.544734824158617, 26.83283592992511],
    "child_1_2": [14.466083025027984, 34.788137217452125, 19.018732389073737]
} }

我想将 json 记录转换为另一个表，以便我可以将它作为包含数组的 customSQL 导入到 Tableau 中。

编辑 1:

这是我正在尝试的查询:

 SELECT * , table_1.column_1 -> 'parent_1' ->  json_object_keys((table_1.column1 ->> 'parent_1')::json) FROM  public.table_1

编辑2:

作为输出，我希望在 Tableau 中读取每个父级的表。在每个表中，我想要:

表:parent_1

Childs  | Value
----------------------------
child_1   | 10.477058081076123
child_1   | 12.570963520289965
child_1   | 11.74866851427825
child_2   | 14.174190848828983
child_2   | 19.3920283059595
child_2   | 17.6712937162821

Answer 1

您需要首先取消嵌套每个“父”的内部结构，然后取消每个元素的数组。

对于第一个表，您可以执行以下操作:

create table parent_1
as
select p.k as child, e.v as value
from table_1
     cross join lateral jsonb_each(column_1 -> 'parent_1') as p(k,v)
     cross join lateral jsonb_array_elements(p.v) as e(v);

要对第二个表执行相同的操作，请替换 column_1 -> 'parent_1'与 column_1 -> 'parent_2'