Update JSONB field with computed data structure in Postgres

Question

I want to aggregate some data from sibling records into a single jsonb field.

Here is an example data structure, very similar to the one I'm working with:

CREATE TABLE so.a (
  id integer,
  foo integer,
  bar integer,
  -- a lot of other data
  CONSTRAINT a_pkey PRIMARY KEY (id)
);

CREATE TABLE so.b (
  id integer,
  a1 integer,
  a2 integer,
  data jsonb,
  CONSTRAINT b_pkey PRIMARY KEY (id),
  CONSTRAINT "a1_fkey" FOREIGN KEY (a1) REFERENCES so.a (id),
  CONSTRAINT "a2_fkey" FOREIGN KEY (a2) REFERENCES so.a (id)
);

and some example data set:

INSERT INTO so.a VALUES
  (1, 42, 24),
  (2, 22, 33),
  (3, 66, 99);

INSERT INTO so.b VALUES
  (1, 1, 2, NULL),
  (2, 2, 3, NULL),
  (3, 3, 1, NULL);

Now I want to fill b.data row with data from sibling records a1 and a2:

data = [{
  "foo": a1.foo,
  "bar": a1.bar
}, {
  "foo": a2.foo,
  "bar": a2.bar
}]

The best solution I came up with is to build a json string using to_json function and string concatenation operator || first and only then convert it to jsonb:

UPDATE
  so.b
SET
  data = ('[{
    "foo": ' || to_json(t1.foo) || ',
    "bar": ' || to_json(t1.bar) || '
  }, {
    "foo": ' || to_json(t2.foo) || ',
    "bar": ' || to_json(t2.bar) || '
  }]')::jsonb
FROM
  so.a t1,
  so.a t2
WHERE 
  t1.id = b.a1 AND
  t2.id = b.a2;

This solution works perfectly well:

SELECT id, data FROM so.b;
-- 1, [{"bar": 24, "foo": 42}, {"bar": 33, "foo": 22}]
-- 2, [{"bar": 33, "foo": 22}, {"bar": 99, "foo": 66}]
-- 3, [{"bar": 99, "foo": 66}, {"bar": 24, "foo": 42}]

But it seems way too clumsy to me.

So I wonder, maybe there is a better way to achieve what I want.

UPD.: Just thought that it may help if I'll also describe my previous attempt to solve this problem.

I wanted to keep thing simple and so I've tried to use array_to_json function:

SET
  data = array_to_json(ARRAY[t1, t2])

But I've ended up with a lot of excess data in my db. And since I couldn't find a way to limit t1 and t2 fields, I gave up on this approach.

Leonid Beschastny · Accepted Answer · 2017-04-28 07:12:14Z

1

I think the only thing you can do here is to use jsonb_build_* functions instead of string concatenation. I.e:

UPDATE so.b SET 
    data = jsonb_build_array( 
        jsonb_build_object( 
            'foo', to_json(t1.foo), 
            'bar', to_json(t1.bar) 
        ), 
        jsonb_build_object( 
            'foo', to_json(t2.foo), 
            'bar', to_json(t2.bar) 
        ) 
    ) 
FROM 
    so.a t1, so.a t2 
WHERE 
    t1.id = b.a1 AND t2.id = b.a2;

edited Apr 28, 2017 at 7:12

Leonid Beschastny

51.6k10 gold badges121 silver badges126 bronze badges

answered Apr 28, 2017 at 6:33

Dmitry Chirkin

1,08410 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update JSONB field with computed data structure in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related