sql - 将 JSON 扩展为以第一行为模板的列

Question

假设您有一个以下形式的 Postgres 表:

 f1  | f2    |   metadata   
-----+-------+-----------------------
 a   | 33    | {"f3": "d", "f4": "e"}
 b   | 20    | {"f3": "d", "f4": "g"}

metadata 列是一个非结构化的 JSON 字段。如何查询此表，以便结果记录包含字段 f1、f2、f3 和 f4，扩展 JSON 以填充这些字段？

我知道 json_populate_record() 可以执行此操作，前提是您事先知道 f3 和 f4 是字段名称。但我没有。我想将第一行的 metadata 中的键名称用作所有其他行的模板。

换句话说:我希望查询的结果列为 f1、f2 + 第一行 JSON 数据的所有键。不符合第一行中的任何其他键将被删除。

Answer 1

没有自然的“第一”行。您需要定义“第一”。假设第一行是 ORDER BY f1, f2。

如果您不知道预期的列数和数据类型，这不能在单个 SQL 语句中完成。 SQL 要求知道返回类型，至少在调用时是这样。但是有多种方法可以用两个语句来完成。

第0步

这是一个合适的测试设置:

CREATE TABLE tbl (f1 text, f2 int, metadata jsonb);
INSERT INTO tbl VALUES
  ('a', 33, '{"f3": "d", "f4": "e"}')  -- "first" row
, ('b', 20, '{"f3": "d", "f4": "g"}')  -- same keys
, ('c', 40, '{"f7": "x", "f4": "o"}')  -- one matching key
, ('d', 50, '{"f3": "o", "f9": "x", "f123": "z"}')  -- one match, two miss
, ('e', 60, '{"x": "d", "y": "g"}')    -- no match
, ('f', 33, '{"f3": 1, "f4": false}'); -- numeric and boolean

第一步

一旦您知道数字、名字和types 结果列，它变得简单，就像你在问题中提到的那样。我建议您创建一个临时表来提供 jsonb_populate_record() 的行类型:

BEGIN;
CREATE TEMP TABLE tmp(f3 text, f4 text) ON COMMIT DROP;

SELECT f1, f2, meta.*
FROM   tbl, jsonb_populate_record(NULL::tmp, metadata) meta;
ROLLBACK;  -- or: COMMIT;

ON COMMIT DROP在事务结束时自动删除表。你可能想要也可能不想要那个。如果这样做，请对这两个命令使用单个事务。

临时表仅在同一个 session 中可见，因此与多个事务执行相同操作时不存在命名冲突。

如果您没有这些信息，它会变得更加复杂。

第二步

您可以对 DO 执行相同的操作命令和动态 SQL:

DO
$do$
BEGIN
EXECUTE 'CREATE TEMP TABLE tmp(f3 text, f4 text) ON COMMIT DROP';
END
$do$;

第三步

由于我们实际上并不知道输出列的数量和名称，因此我们从第一行中提取该信息。假设:

• 所有列的数据类型都是文本。
• “第一”行在 jsonb 列中至少有一个键。
• 现有列名称“f1”和“f2”不会作为 JSON 列中的键重复。 (Postgres 允许在输出列中使用重复的名称，但一些客户对此有问题 - 这很令人困惑。)

DO
$do$
BEGIN

EXECUTE (
   SELECT (SELECT 'CREATE TEMP TABLE tmp('
                || string_agg(quote_ident(k), ' text, ')  -- f3 text, f4 
                || ' text) ON COMMIT DROP'
           FROM   jsonb_object_keys(metadata) k)
   FROM   tbl
   ORDER  BY f1, f2
   LIMIT  1
   );

END
$do$;

SELECT f1, f2, meta.*
FROM   tbl, jsonb_populate_record(NULL::tmp, metadata) meta;

瞧。

请务必使用 quote_ident() 正确转义键名或类似的。

如果列名事先已知......
( addressing your comment )，您可以简单地:

SELECT f1, f2, metadata->>'f3', metadata->>'f4'
FROM   tbl;

不过，对于宽行，jsonb_populate_record() 更方便。您仍然可以使用动态解决方案，或者保留一个表或类型并使用它。

备选

如果您的第二个命令可以依赖您的第一个命令，您也可以动态构建简单语句并在另一个调用中执行它:

SELECT (SELECT 'SELECT f1, f2, metadata->>'
             || string_agg(format('%1$L AS %1$I', k), ', metadata->>')
             || ' FROM tbl'
        FROM   jsonb_object_keys(metadata) k)
FROM   tbl
ORDER  BY f1, f2
LIMIT  1;

以文本形式返回上述简单查询。将其作为第二条命令执行……执行速度可能会快一点，但它需要两次到服务器的往返，而第一个解决方案只需一次就可以了……你决定。

使用 format()这里是为了简化安全查询字符串连接。