时间戳

首页 > 解决方案 > 如何加入来自 jsonb 列的嵌套值？

sql - 如何加入来自 jsonb 列的嵌套值？

问题描述

我有一个包含这些表的 PostgreSQL 11 数据库：

CREATE TABLE stats (
   id integer NOT NULL,
   uid integer NOT NULL,
   date date NOT NULL,
   data jsonb DEFAULT '[]'::json NOT NULL
);
INSERT INTO stats(id, uid, date, data) VALUES
   (1, 1, '2020-10-01', '{"somerandomhash":{"source":"thesource"}}');

CREATE TABLE links(
   id integer NOT NULL,
   uuid uuid NOT NULL,
   path text NOT NULL
);
INSERT INTO links(id, uuid, path) VALUES
   (1, 'acbd18db-4cc2-f85c-edef-654fccc4a4d8', 'thesource');

我的目标是从表中创建一个新表reports，但使用表中的新键。它看起来像这样：datastatslinks

CREATE TABLE reports(
    id integer NOT NULL,
    uid integer NOT NULL,
    date date NOT NULL,
    data jsonb DEFAULT '[]'::json NOT NULL
);

INSERT INTO reports(id, uid, date, data) VALUES
   (1, 1, 2020-10-01, {"uuid":{"source":"thesource"});

为此，我尝试左连接表links以检索uuid列值 - 没有运气：

SELECT s.uid, s.date, s.data->jsonb_object_keys(data)->>'source' as path, s.data->jsonb_object_keys(data) as data, l.uuid
FROM stats s LEFT JOIN links l ON s.data->jsonb_object_keys(data)->>'source' = l.path

我尝试在左连接中使用 s.data->jsonb_object_keys(data)->>'source' 的结果，但出现错误：

ERROR:  set-returning functions are not allowed in JOIN conditions

我尝试使用LATERAL但仍然无效。
如何使这项工作？

标签： sqlpostgresqlleft-joinjsonbset-returning-functions

解决方案

jsonb_object_keys()是一个 set-returning 函数，不能按照您的方式使用 - 正如错误消息告诉您的那样。更重要的是，json_object_keys()返回顶级key(s)，但您似乎只对value感兴趣。请尝试jsonb_each()：

SELECT s.id
     , s.uid
     , s.date
     , jsonb_build_object(l.uuid::text, o.value) AS new_data
FROM   stats s
CROSS  JOIN LATERAL jsonb_each(s.data) o  -- defaults to column names (key, value)
LEFT   JOIN links l ON l.path = o.value->>'source';

db<>在这里摆弄

jsonb_each()返回顶级键和值。仅使用value继续。

嵌套的 JSON 对象似乎具有常量键名'source'。所以加入条件是l.path = o.value->>'source'。

最后，用 构建新jsonb值jsonb_build_object()。

虽然这如所展示的那样有效，但仍有几个问题：

  • 以上假设在stats.data. 如果没有，你必须定义要做什么......

  • 以上假设 table 中总是有一个匹配项links。如果没有，你必须定义要做什么......

  • 最重要的是： 如果data您认为它是常规的，请考虑使用普通的“uuid”列（或将其删除，因为值在表中links）和普通的列“源”来替换该jsonb列。更简单，更高效。

推荐阅读