json - 将 json 文件导入到具有单个 json 列的 postgres 表中

Question

我一直在尝试将 json 文件作为单个 json 列加载到 postgres 中。

表格:创建表 book(values json);

文件看起来像这样:

[
  {
    "isbn": "846896359-3",
    "title": "Jungle Book 2, The",
    "price": 22.05,
    "date": "12/28/2017",
    "authors": [
      {
        "first": "Marlène",
        "last": "Ashley",
        "age": 38
      },
      {
        "first": "Miléna",
        "last": "Finley",
        "age": 37
      },
      {
        "first": "Stévina",
        "last": "Bullus",
        "age": 44
      }
    ],
    "publisher": {
      "name": "Youspan",
      "address": {
        "street": "Iowa",
        "number": "853",
        "city": "München",
        "country": "Germany"
      },
      "phone": "361-191-8111"
    }
  },
  {
    "isbn": "558973823-7",
    "title": "Star Trek III: The Search for Spock",
    "price": 36.58,
    "date": "4/19/2017",
    "authors": [
      {
        "first": "Uò",
        "last": "Ibel",
        "age": 26
      },
      {
        "first": "Mélys",
        "last": "Grasner",
        "age": 36
      },
      {
        "first": "Mylène",
        "last": "Laven",
        "age": 40
      },
      {
        "first": "Pò",
        "last": "Lapsley",
        "age": 37
      }
    ],
    "publisher": {
      "name": "Chatterbridge",
      "address": {
        "street": "Dennis",
        "number": "1",
        "city": "São Tomé",
        "country": "Sao Tome and Principe"
      },
      "phone": "845-226-0017"
    }
  }
]

尝试了复制命令，但它抛出一个 'Token ""is invalid.' 错误。我也尝试了一些其他的解决方案

Answer 1

所以，我终于让它工作了。首先，我使用从文件中删除换行符 tr -d '\n' < yourfile.txt

然后我运行了以下脚本:

create table Bookstemp(values text);                    

copy Bookstemp from 'BOOKS_DATANew.json';

create table books(valjson json);

Insert into books
select values
from   (
           select json_array_elements(replace(values,'\','\\')::json) as values 
           from   Bookstemp
       ) a;