在 PostgreSQL 中从 JSON 文件创建表

圆环

我只是在学习如何使用 PostgreSQL 和 JSON。我遇到了这个很棒的教程，但语法是为 SQL Server 制作的。我正在尝试获取以下 JSON 文件并开始将其解析为一个包含小队、姓名、年龄和权力列的表。

JSON 代码是

CREATE TABLE heroes (
    id serial NOT NULL PRIMARY KEY,
    info json NOT NULL
);

insert into heroes (info)
values (('
      {
  "squadName": "Super hero squad",
  "homeTown": "Metro City",
  "formed": 2016,
  "secretBase": "Super tower",
  "active": true,
  "members": [
    {
      "name": "Molecule Man",
      "age": 29,
      "secretIdentity": "Dan Jukes",
      "powers": [
        "Radiation resistance",
        "Turning tiny",
        "Radiation blast"
      ]
    },
    {
      "name": "Madame Uppercut",
      "age": 39,
      "secretIdentity": "Jane Wilson",
      "powers": [
        "Million tonne punch",
        "Damage resistance",
        "Superhuman reflexes"
      ]
    },
    {
      "name": "Eternal Flame",
      "age": 1000000,
      "secretIdentity": "Unknown",
      "powers": [
        "Immortality",
        "Heat Immunity",
        "Inferno",
        "Teleportation",
        "Interdimensional travel"
      ]
    }
  ]
}
    '::json));

我可以毫无问题地访问 JSON 的第一级信息，例如

SELECT info -> 'squadName' AS squad from heroes; 或者 SELECT info -> 'active' AS active from heroes;

但是，当试图深入研究 JSON 时，我最终得到了一行、正确的小队名称和成员名称的 NULL：

SELECT info -> 'squadName' AS Squad,
        info ->'members' ->> 'name' AS Name
from heroes;

本教程用于 CROSS APPLY OPENJSON(..)处理此问题，但我不确定在 PostgreSQL 中该做什么。

任何帮助，将不胜感激。我将此用作学习练习。

粘点

您可以对进行横向交叉连接json_array_elements()。

SELECT h.info->>'squadName' AS squad,
       m.m->>'name' AS name
       FROM heroes h
            CROSS JOIN LATERAL json_array_elements(h.info->'members') m
                                                                      (m);

数据库<>小提琴

但作为旁注：JSON 的模式对我来说看起来非常静态。您应该考虑不要滥用 JSON，而是使用关系方式，如（查找和/或链接）表和列。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。