title: How to join jsonb array elements in Postgres?
body: <p>I am using Postgres 9.5, and I have the following tables:</p>
  
  <p><strong>Users</strong></p>
  
  <ul>
  <li>id UUID</li>
  <li>name TEXT</li>
  </ul>
  
  <p><strong>Images</strong></p>
  
  <ul>
  <li>id UUID</li>
  <li>key TEXT</li>
  <li>width INTEGER</li>
  <li>height INTEGER</li>
  </ul>
  
  <p><strong>Posts</strong></p>
  
  <ul>
  <li>id UUID</li>
  <li>title TEXT</li>
  <li>author_id UUID</li>
  <li>content JSONB</li>
  </ul>
  
  <p>The posts' content is like:</p>
  
  <pre><code>[
    { "type": "text", "text": "learning pg" },
    { "type": "image", "image_id": "8f4422b4-3936-49f5-ab02-50aea5e6755f" },
    { "type": "image", "image_id": "57efc97c-b9b4-4cd5-b1e1-3539f5853835" },
    { "type": "text", "text": "pg is awesome" }
  ]
  </code></pre>
  
  <p>Now I want to join the image type of content, and populate them with <code>image_id</code>, like:</p>
  
  <pre><code>{
    "id": "cb1267ca-b1ac-4daa-8c7e-72d4c000e9fa",
    "title": "Learning join jsonb in Postgres",
    "author_id": "deba01b7-ec58-4cc2-b3ae-7dc42e582767",
    "content": [
      { "type": "text", "text": "learning pg" },
      {
         "type": "image",
         "image": {
           "id": "8f4422b4-3936-49f5-ab02-50aea5e6755f",
           "key": "/upload/test1.jpg",
           "width": 800,
           "height": 600
         }
      },
      {
         "type": "image",
         "image": {
           "id": "57efc97c-b9b4-4cd5-b1e1-3539f5853835",
           "key": "/upload/test2.jpg",
           "width": 1280,
           "height": 720
         }
      },
      { "type": "text", "text": "pg is awesome" }
    ]
  }
  </code></pre>
  
  <p>Here is my test sql file:</p>
  
  <pre><code>CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
  
  DROP TABLE IF EXISTS Users;
  DROP TABLE IF EXISTS Images;
  DROP TABLE IF EXISTS Posts;
  
  CREATE TABLE Users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text NOT NULL
  );
  
  CREATE TABLE Images (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    key TEXT,
    width INTEGER,
    height INTEGER,
    creator_id UUID
  );
  
  CREATE TABLE Posts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title TEXT,
    author_id UUID,
    content JSONB
  );
  
  
  DO $$
  DECLARE user_id UUID;
  DECLARE image1_id UUID;
  DECLARE image2_id UUID;
  
  BEGIN
  
  INSERT INTO Users (name) VALUES ('test user') RETURNING id INTO user_id;
  INSERT INTO Images (key, width, height, creator_id) VALUES ('upload/test1.jpg', 800, 600, user_id) RETURNING id INTO image1_id;
  INSERT INTO Images (key, width, height, creator_id) VALUES ('upload/test2.jpg', 600, 400, user_id) RETURNING id INTO image2_id;
  INSERT INTO Posts (title, author_id, content) VALUES (
    'test post',
    user_id,
    ('[ { "type": "text", "text": "learning pg" }, { "type": "image", "image_id": "' || image1_id || '" }, { "type": "image", "image_id": "' || image2_id || '" }, { "type": "text", "text": "pg is awesome" } ]') :: JSONB
  );
  
  END $$;
  </code></pre>
  
  <p>Is there any way to implement this requirement?</p>