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.

Roman Tkachuk · Accepted Answer · 2017-03-06 08:54:58Z

3

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

edited Mar 6, 2017 at 8:54

answered Mar 6, 2017 at 8:49

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nayana Madhu Over a year ago

Can you please suggest a way by without hard coding?

Roman Tkachuk Over a year ago

If you want to use your db -

WITH el AS (SELECT jsonb_array_elements(your_json_field) AS el FROM your_json_table ) SELECT el->>'personName' as personName, ...

. If you mean about dynamic json structure you probably need stackoverflow.com/questions/42403321/…

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related