To read json column from postgres like sql database

read_json_from_sql.py

# -*- coding: utf-8 -*-

import json

import pandas as pd

from db.oracle_client import get_oracle_conn

file_name = "data.csv"


# Filter jsons.
def filter_errors(data):
    try:
        json.loads(data['JSON_COLUMN'].read())
        return True
    except ValueError as value_error:
        return False


if __name__ == '__main__':

    # Read Sql
    sql = "select JSON_COLUMN from table.name where condition"
    df = pd.read_sql(sql=sql, con=get_oracle_conn())
    print("Shape of data frame : ", df.shape)

    # Validate jsons.
    filtered_df = df[df.apply(filter_errors, axis=1)]

    # Convert json fields to columns
    a = filtered_df['JSON_COLUMN'].apply(str).apply(json.loads).values.tolist()
    json_df = pd.DataFrame.from_records(a)

    # Save to csv.
    json_df.to_csv(file_name)