How to join jsonb array elements in Postgres?

sql json postgresql jsonb

11,275

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?

Unnest the jsonb array, producing 1 row per array element:
```
jsonb_array_elements(p.content) AS c(elem)
```
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.}
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.
Re-aggregate the modified elements to a new content column with jsonb_agg().
^{Would work with a plain ARRAY constructor as well.}
Unconditionally LEFT JOIN LATERAL the result to posts and select all columns, only replace p.content with the generated replacement c.content
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.

11,275

Author by

user2331095

Updated on June 14, 2022

Comments

user2331095 almost 2 years

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?

shawmanz32na almost 7 years

Big up for calling out the usage of jsonb_array_elements - it helped me find a solution to our only-slightly-related problem