How to convert a nested json column from postgresql database to dataframe with python or with query?

Question

How to convert a nested json from a postgresql table to dataframe with python or using direct query?

The json object looks like

[
  {
    "personName": "Aly Michalka",
    "characterName": "Aly Michalka",
    "creditType": "Actor"
  },
  {
    "personName": "Malcolm McDowell",
    "characterName": "Malcolm McDowell",
    "creditType": "Actor"
  },
  {
    "personName": "Lisa Kudrow",
    "characterName": "Lisa Kudrow",
    "creditType": "Actor"
  },
  {
    "personName": "Cam Gigandet",
    "characterName": "Cam Gigandet",
    "creditType": "Actor"
  },
  {
    "personName": "Patricia Clarkson",
    "characterName": "Patricia Clarkson",
    "creditType": "Actor"
  },
  {
    "personName": "Thomas Haden Church",
    "characterName": "Thomas Haden Church",
    "creditType": "Actor"
  },
  {
    "personName": "Amanda Bynes",
    "characterName": "Amanda Bynes",
    "creditType": "Actor"
  },
  {
    "personName": "Penn Badgley",
    "characterName": "Penn Badgley",
    "creditType": "Actor"
  },
  {
    "personName": "Emma Stone",
    "characterName": "Emma Stone",
    "creditType": "Actor"
  },
  {
    "personName": "Will Gluck",
    "characterName": "Will Gluck",
    "creditType": "Director"
  }
]

This entire json object should correspond to a particular column of dataframe.

Answer 1

To split your array you can use jsonb_array_elements function. To access to each field operator ->> - field->>'json field name'. If you use json type (not jsonb) just change function name from jsonb_array_elements to json_array_elements. All possible json/jsonb functions you can found on https://www.postgresql.org/docs/9.5/static/functions-json.html

WITH x AS (
SELECT '[{"personName":"Aly Michalka","characterName":"Aly Michalka","creditType":"Actor"},{"personName":"Malcolm McDowell","characterName":"Malcolm McDowell","creditType":"Actor"},{"personName":"Lisa Kudrow","characterName":"Lisa Kudrow","creditType":"Actor"},{"personName":"Cam Gigandet","characterName":"Cam Gigandet","creditType":"Actor"},{"personName":"Patricia Clarkson","characterName":"Patricia Clarkson","creditType":"Actor"},{"personName":"Thomas Haden Church","characterName":"Thomas Haden Church","creditType":"Actor"},{"personName":"Amanda Bynes","characterName":"Amanda Bynes","creditType":"Actor"},{"personName":"Penn Badgley","characterName":"Penn Badgley","creditType":"Actor"},{"personName":"Emma Stone","characterName":"Emma Stone","creditType":"Actor"},{"personName":"Will Gluck","characterName":"Will Gluck","creditType":"Director"}]'::jsonb AS a
), el AS (
   SELECT jsonb_array_elements(a) AS el FROM x
)
SELECT el->>'personName' as personName,
       el->>'characterName',
       el->>'creditType'
  FROM el



     personname      |    charactername    | credittype 
---------------------+---------------------+------------
 Aly Michalka        | Aly Michalka        | Actor
 Malcolm McDowell    | Malcolm McDowell    | Actor
 Lisa Kudrow         | Lisa Kudrow         | Actor
 Cam Gigandet        | Cam Gigandet        | Actor
 Patricia Clarkson   | Patricia Clarkson   | Actor
 Thomas Haden Church | Thomas Haden Church | Actor
 Amanda Bynes        | Amanda Bynes        | Actor
 Penn Badgley        | Penn Badgley        | Actor
 Emma Stone          | Emma Stone          | Actor
 Will Gluck          | Will Gluck          | Director