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

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