How to join jsonb array elements in Postgres?

Question

I am using Postgres 9.5, and I have the following tables:

Users

• id UUID
• name TEXT

Images

• id UUID
• key TEXT
• width INTEGER
• height INTEGER

Posts

• id UUID
• title TEXT
• author_id UUID
• content JSONB

The posts' content is like:

[
  { "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" }
]

Now I want to join the image type of content, and populate them with image_id, like:

{
  "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" }
  ]
}

Here is my test sql file:

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 $$;

Is there any way to implement this requirement?

Answer 1

Assuming at least Postgres 9.5, this will do the job:

SELECT jsonb_pretty(to_jsonb(p)) AS post_row_as_json
FROM  (
   SELECT id, title, author_id, c.content
   FROM   posts p
   LEFT   JOIN LATERAL (
      SELECT jsonb_agg(
               CASE WHEN c.elem->>'type' = 'image' AND i.id IS NOT NULL
                    THEN elem - 'image_id' || jsonb_build_object('image', i)
                    ELSE c.elem END) AS content
      FROM   jsonb_array_elements(p.content) AS c(elem)
      LEFT   JOIN images i ON c.elem->>'type' = 'image'
                          AND i.id = (elem->>'image_id')::uuid
      ) c ON true
   ) p;

How?

1. Unnest the jsonb array, producing 1 row per array element:

   jsonb_array_elements(p.content) AS c(elem)
   

2. For each element LEFT JOIN to images on the conditions that
   a. The key 'type' has the value 'image': c.elem->>'type' = 'image'
   b. The UUID in image_id matches: i.id = (elem->>'image_id')::uuid
   ^{Note that an invalid UUID in content would raise an exception.}

3. For image types, where a matching image was found

   c.elem->>'type' = 'image' AND i.id IS NOT NULL
   

   remove the key 'image_id' and add the related image row as jsonb value:

   elem - 'image_id' || jsonb_build_object('image', i)
   

   Else keep the original element.

4. Re-aggregate the modified elements to a new content column with jsonb_agg().
   ^{Would work with a plain ARRAY constructor as well.}

5. Unconditionally LEFT JOIN LATERAL the result to posts and select all columns, only replace p.content with the generated replacement c.content

6. In the outer SELECT, convert the whole row to jsonb with a simple to_jsonb().
   ^{jsonb_pretty() is totally optional for human-readable representation.}

All jsonb functions are documented in the manual here.