将 JSON 数据插入 postgresql 查询成功，但输出意外

Inserting JSON data to postgresql query successful, but output is unexpected

提问by Ambidextrous

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

我正在尝试将一些 JSON 数据插入到 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/"}');

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.

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

Here is the output:

这是输出：

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

有谁知道为什么输出是这样的？知道我应该怎么做才能纠正这个问题吗？

采纳答案by mathieu

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

首先，在 postgresql 中，如果你真的想要你的字段名和表名大写，你需要像这样创建表：

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

Then the query would be like :

然后查询将是这样的：

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

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

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.

如您所见，由于 json 和 sql 表中的字段名称不匹配， multiprocess 被留空。要摆脱它，您可以执行外部脚本或 postgresql 函数。

For example:

例如：

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';

Then, you can do :

然后，你可以这样做：

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/

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.

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

回答by cur4so

json keys should match fields in the created table (prefix ended with "." is treated as a table name, w/o it)

json 键应该匹配创建的表中的字段（以“.”结尾的前缀被视为表名，没有它）

CREATE TABLE TEST (MULTIPROCESS VARCHAR(20), HTTP_REFERER VARCHAR(50));
INSERT INTO TEST SELECT MULTIPROCESS, HTTP_REFERER FROM json_populate_record(NULL::test, '{"multiprocess": true,"http_referer": "http://localhost:9000/"}');

http_referer should be in small letters in json_populate_record()

http_referer 在 json_populate_record() 中应该是小写字母

http://sqlfiddle.com/#!15/ec202/1

http://sqlfiddle.com/#!15/ec202/1