Ecto/SQL: how to UPDATE an object in jsonb array with a certain criteria?

Questions / Help
1

If somebody could help me with Ecto/SQL I would really really appreciate it!

I have an embeds_many schema and in the DB it’s a jsonb or array of json objects.

I need to update all objects in jsonb with a certain criteria, like:
if obj.id == 1 or or obj.name == "Old Name", update field "name:" to a "New Name"

So I am able to GET all needed objects by:

    from(p in Post,
      inner_lateral_join:
        item in fragment(
          "SELECT * FROM jsonb_to_recordset(to_jsonb(?)) as item(id integer, name text)",
          c.comments
        ),
      select: %Comment{
        id: item.id,
        name: item.name
      },
      where: item.id == 1 or item.name == "Old Name"
    )
    |> Repo.all()

So I got them:

[
  %Comment{
    id: 1,
    name: "Old Name"
  },
  %Comment{
    id: 2,
    name: "Old Name"
  }
]

But how can do the actual UPDATE? I guess raw SQL should be involved here somehow, but I wish I would know…

Thank you all in advance!

P.S. I asked the same question in Elixir Slack. Sorry if you read it again here!

2

:wave:

It might be easier to read the data from DB and do an upsert in the same transaction than updating in the same query when dealing with jsonb.

What’s the schema for the posts table?
Does that look about right?

create table posts (id bigint, comments jsonb);
insert into posts (id, comments) values
(1, '[{"id":1,"name":"Old Name"},{"id":2,"name":"Old Name"},{"id":3,"name":"New Name"}]'),
(2, '[{"id":4,"name":"Name"},{"id":5,"name":"Old Name"}]'),
(3, '[{"id":6,"name":"Name"}]');

If it does, then a query like

-- select posts to update, and update comments to have name=New Name
with updated(id, comments) as (
  select p.id, to_jsonb(array_agg(c || '{"name":"New Name"}'::jsonb))
  from posts p, jsonb_array_elements(p.comments) c
  where (c ->> 'name') = 'Old Name' or (c -> 'id')::integer = 1
  group by p.id
 ),
-- select unaffected comments from updated posts
ok(id, comments) as (
   select id, jsonb_path_query_array(comments, '$[*] ? (@.name != "Old Name") ? (@.id != 1)')
   from posts
   where id in (select id from updated)
 )
-- concat updated and ok comments
select ok.comments || updated.comments from ok inner join updated on ok.id = updated.id;

-- result:
-- "[{""id"": 3, ""name"": ""New Name""}, {""id"": 1, ""name"": ""New Name""}, {""id"": 2, ""name"": ""New Name""}]"
-- "[{""id"": 4, ""name"": ""Name""}, {""id"": 5, ""name"": ""New Name""}]"

might work.

Although I’d suggest doing this in Elixir:

  • fetch affected posts
  • iterate comments updating the name
  • and upsert

All in the same transaction.

3 Likes
3

+1 for @ruslandoga’s suggestion to do this in Elixir: ultimately, each affected Post will need to have its comments field rewritten with updated data.

Also consider your schema design; wanting to query / update individual elements in an array-ish field like this frequently means you’d be better off be using a separate table and standard relationships. For instance, if there was a separate comments table your original question would be a straightforward update_all query.

2 Likes
4

@ruslandoga and @al2o3cr
Thank you so much!

Yeah, I agree! It gives me so much unnecessary pain, that having a separate table is the best and easiest solution!

Thank you again all! I really appreciate it!

5

Wow! This is great! And actually works!
Thank you, Ruslan!