成功将JSON数据插入到PostgreSQL查询中，但是输出是意外的 [英] Inserting JSON data to postgresql query successful, but output is unexpected

问题描述

我正在尝试将一些JSON数据插入到postgresql中的表中.

I am trying to insert some JSON data to a table in postgresql.

JSON DATA:
{
 "wsgi.multiprocess": true,
 "HTTP_REFERER": "http://localhost:9000/"
}

因此，为此，我正在执行以下步骤:

So, to do this, I am doing these steps:

CREATE TABLE TEST (MULTIPROCESS VARCHAR(20), HTTP_REFERER VARCHAR(50));

INSERT INTO TEST SELECT * FROM json_populate_record(NULL::test, '{"wsgi.multiprocess": true,"HTTP_REFERER": "http://localhost:9000/"}');

第一步将创建一个表，而下一步应将JSON数据插入表中.查询成功完成，但是当我尝试查看表中的数据时，它只是一个管道.

The first step creates a table, while the next one should insert JSON data into the table. The query completes successfully, but when I try to see the data inside the table, it just a single pipe.

这是输出:

任何人都知道为什么这样的输出?知道我该怎么做才能纠正这个问题吗?

Anybody knows why is the output like this? Any idea what I should do to rectify this?

推荐答案

首先，在postgresql中，如果您确实希望字段名和表名是大写的，则需要这样创建表:

First, in postgresql if you really want your fieldname and table name uppercase, you need to create the table like this:

CREATE TABLE "TEST" ("MULTIPROCESS" VARCHAR(20), "HTTP_REFERER" VARCHAR(50));

然后查询将类似于:

SELECT * FROM json_populate_record(NULL::"TEST", '{"wsgi.multiprocess": true,"HTTP_REFERER": "http://localhost:9000/"}'); 

 MULTIPROCESS |      HTTP_REFERER      
--------------+------------------------
              | http://localhost:9000/

如您所见，由于json和sql表中的字段名称不匹配，因此multiprocess留为空白.要摆脱这种情况，您可以执行外部脚本或Postgresql函数.

As you can see, multiprocess is leaved blank because the field name in json and in the sql table dont match. To get rid of that you can do an external script or a postgresql function.

例如:

CREATE OR REPLACE
  FUNCTION replace_json_keys(IN js TEXT) 
  RETURNS json 
  STABLE 
  AS
$$
BEGIN
  js := replace(js, '"wsgi.multiprocess"', '"MULTIPROCESS"');
  return js::json;
END;
$$ LANGUAGE 'plpgsql';

然后，您可以做:

SELECT * FROM json_populate_record(NULL::"TEST", replace_json_keys('{"wsgi.multiprocess": true,"HTTP_REFERER": "http://localhost:9000/"}'));

 MULTIPROCESS |      HTTP_REFERER      
--------------+------------------------
 true         | http://localhost:9000/

但是，最好的解决方案不是使用replace()，而是使用json_each()将键和值拆分为两个数组，并在其中使用"case when"进行循环以将json键映射到sql键，然后返回准备插入的新json对象(带有json_object(keys[], values[])).

However, the best solution wouldn't be to use replace() but to use json_each() to split the keys and values in two arrays, do a loop with a "case when" in it to map the json keys to the sql keys and then return a new json object (with json_object(keys[], values[])) ready to insert.

这篇关于成功将JSON数据插入到PostgreSQL查询中，但是输出是意外的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！