PostGRE将列JSONB转换在另一个表中，其中列是密钥

Question

我需要在这种情况下使用python创建一个脚本，以获取一个表的列jsonb a创建另一个表，其中这些列都是此json的所有键。

例如：

来自

id  | optional
1   | {"a":"4", "b":"5"}
2   | {}
3   | {"a":"8", "c":"9", "d":"9"}

to

id  |  a   |   b   |  c  |  d
1   |  4   |   5   |     |  
3   |  8   |       |  9  |  9

我使用此查询获得了键：

select jsonb_object_keys(optional) as key from table group by key

我在python中使用以下代码来创建一个用键作为列的表

    connection = psycopg2.connect( host=host, database=database, user=user, password=password)
    try:      
        columns = "("
        for column in keys:
            columns+=column+" TEXT "+','
        columns = columns[0:len(columns)-1]
        columns += ");"
        query = "CREATE TABLE " + table +" "
        query +=  columns
        print query
        cur = connection.cursor()
        cur.execute(query)
        connection.commit()
        cur.close()

我得到了使用此查询需要放入另一个表的数据：

select id, optional->'a',...  from table where optional<>'{}'

就我而言，我有大约31个键，所以上面的查询很大，另一方面，如果我想将此脚本重复使用到另一种情况下，我可能需要更改此查询。

所以我想知道是否还有另一种更优雅，更通用的方式。即使解决方案仅与Postgres一起使用Python也不需要使用Python，这对我也有好处

有什么想法吗？

预先感谢

Answer 1

您可能会在此答案中描述的Postgres解决方案中被认为（请参阅广义解决方案）。

示例源表：

drop table if exists my_table;
create table my_table(id int primary key, data jsonb);
insert into my_table values
(1, '{"a":"4", "b":"5"}'),
(2, '{}'),
(3, '{"a":"8", "c":"9", "d":"9"}');

使用该函数：

select create_jsonb_flat_view('my_table', 'id', 'data');
select * from my_table_view;
 id | a | b | c | d 
----+---+---+---+---
  1 | 4 | 5 |   | 
  2 |   |   |   | 
  3 | 8 |   | 9 | 9
(3 rows)

---

您可以根据平面视图创建一个新表：

create table my_new_table as
select *
from my_table_view
order by id;