将JSON数据插入postgresql查询成功，但输出异常

Question

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

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

因此，为了做到这一点，我正在执行以下步骤：

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数据插入到表中。查询成功完成，但是当我尝试查看表中的数据时，它只有一个管道。

下面是输出：

​

有人知道为什么输出是这样的吗？你知道我该怎么做来纠正这个问题吗？

Answer 1

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

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/

如您所见，multiprocess保留为空，因为json和sql表中的字段名不匹配。要摆脱这一点，您可以使用外部脚本或postgresql函数。

例如：

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[]))，准备插入。

Answer 2

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/"}');

json_populate_record()中的http_referer应为小写字母

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