postgresql - 如何使用 python 或查询将嵌套的 json 列从 postgresql 数据库转换为数据框？

Question

如何使用 python 或使用直接查询将嵌套的 json 从 postgresql 表转换为数据框？

json对象看起来像

[
  {
    "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"
  }
]

整个 json 对象应该对应于数据框的特定列。

Answer 1

要拆分数组，您可以使用 jsonb_array_elements 函数。访问各个字段操作符->> - field->>'json字段名'。如果您使用 json 类型(不是 jsonb)，只需将函数名称从 jsonb_array_elements 更改为 json_array_elements。您可以在 https://www.postgresql.org/docs/9.5/static/functions-json.html 上找到所有可能的 json/jsonb 函数

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