Create SQL view from a table that contains a jsonb column.

jsonb-to-sql-view.py

def jsonb_to_sql_view(conn, table_name):
    """Create SQL view from a table that contains a jsonb column.

        @param conn Postgres connection.
        @param table_name Table name containing the jsonb.
    """


    cur = conn.cursor()

    # Retrieve the column names you want to use first.
    cur.execute("SELECT keys FROM column_name WHERE table_name = '{}'".format(table_name))
    keys = cur.fetchall()[0][0]

    view = 'DROP VIEW IF EXISTS {}_v;\n'.format(table_name)
    view += 'CREATE OR REPLACE VIEW {}_v AS\n SELECT\n'.format(table_name)

    for (i, key) in enumerate(keys):
        # Check if it is the last key to not add trailing comma
        if i == (len(keys.keys()) - 1):
            view +=  '(jsonb_data ->> \'{}\')::{} AS "{}" \n'.format(key, keys[key], key)
        else:
            view +=  '(jsonb_data ->> \'{}\')::{} AS "{}",\n'.format(key, keys[key], key)

    view += ' FROM {};\n\n'.format(table_name)

    cur.execute(view)
    conn.commit()