title: How to join on a nested value from a jsonb column?
body: <p>I have a PostgreSQL 11 database with these tables:</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>My goal is to create a new table <code>reports</code> with <code>data</code> from the <code>stats</code> table, but with a new key from the <code>links</code> table. It will look like this:</p>
  
  <pre><code>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"});
  </code></pre>
  
  <p>To this end, I tried to left join the table <code>links</code> in order to retrieve the <code>uuid</code> column value - without luck:</p>
  
  <pre><code>SELECT s.uid, s.date, s.data-&gt;jsonb_object_keys(data)-&gt;&gt;'source' as path, s.data-&gt;jsonb_object_keys(data) as data, l.uuid
  FROM stats s LEFT JOIN links l ON s.data-&gt;jsonb_object_keys(data)-&gt;&gt;'source' = l.path
  </code></pre>
  
  <p>I tried to use the result of s.data->jsonb_object_keys(data)->>'source' in the left join, but got the error:</p>
  
  <blockquote>
  <pre><code>ERROR:  set-returning functions are not allowed in JOIN conditions
  </code></pre>
  </blockquote>
  
  <p>I tried using <code>LATERAL</code> but still not valid result.<br>
  How to make this work?</p>