将包含复杂JSON的CSV导入Postgres

Question

我有一个CSV文件，其中一个列json_dic包含复杂的JSON，在字典中的列表中有许多嵌套字段

把这个文件上传到Postgres数据库的最好方法是什么，这样我就可以访问/搜索特定的字段并从SQL查询中检索这些数据？在

例如，我有数百万行以下内容：

JSON列json_doc如下所示，但对于其他行则不一致，即在不同的位置有其他嵌套字典等：

{"id": "0436d9b9305d", "base": {"ateco": [{"code": "46.11.03", "rootCode": "G", "description": "Agenti e rappresentanti di animali vivi"}], "founded": "1972-07-10", "legalName": "COBEM - S.R.L.", "legalForms": [{"name": "Società Di Capitale", "level": 1}, {"name": "Società A Responsabilità Limitata", "level": 2}]}, "name": "COBEM - S.R.L.", "people": {"items": [{"name": "45808b0b5b5affa871c8e91169bb10c6930fac56", "givenName": "64e4393f477394f11f6477ca76395ed469548865", "familyName": "68ee44f14dc54d664dffe63195d42a14988b69bb"}]}, "country": "it", "locations": {}}

我如何将它以当前的CSV格式或JSON格式上载到PostgreSQL？我找了好几天才找到答案，但一无所获。我尝试过使用COPY、usepandas、json_normalize等，我希望能够执行一个join来将ID号与另一个表相匹配，然后从这个复杂的文件中提取一些信息。这有可能吗？在

我的一次尝试：

^{pr2}$

然后在我的psql终端中：

SN_ITA_test=# \COPY public."ITA_test" FROM 'data.json';

产量：

  ERROR:  extra data after last expected column
CONTEXT:  COPY ITA_test, line 1: "{"id": "4002736", "json_doc": "{\"id\": \"0436d9b9305d\", \"base\": {\"ateco\": [{\"code\": \"46.11...."

Answer 1

由于您有一个巨大的CSV文件，而且您的需求似乎很重要，所以我建议将位于https://github.com/fadado/CSV的csv2json程序与jq结合使用。您将使用jq分别处理csv2json生成的每一行。在

如果您不想单独处理每个CSV记录（即，不转换整个CSV文件），您可能希望考虑一个只有jq的解决方案，使用jq中的CSV解析器witten，例如https://gist.github.com/pkoppstein/bbbbdf7489c8c515680beb1c75fa59f2

Answer 2

如果您的数据是CSV格式，您必须告诉COPY：

\copy public."ITA_test" FROM 'data.json' (FORMAT 'csv')

默认格式是text，其中的字段由制表符分隔。在

另一方面，如果文件的一行仅仅是一个大的JSON，那么可以像这样加载它：

^{pr2}$